How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?

RICH937

Board Regular
Joined
Apr 15, 2023
Messages
59
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi Everyone,

I am at a loss which way I should proceed, and I am hoping someone can lend a hand. I am trying to conduct pareto (80|20) analysis on a data set filtered for either 3,2 or 1 variable based on average monthly volume. Those variables are "Channel," "Product," and "Customer." Each analysis is then used to determine whether one variable is in the top 80% of volume for that subset. Ultimately, all the analysis will end up in Power BI for reporting. I have no problem doing this in excel using a table to run calculations on summaries from a pivot table, but I want the data to both calculate and refresh with each addition to the root dataset. That dataset contains product volume and sales data for between 5 and 7 years. There are about 20 additional non-date variable columns like "Sales Team," "State," and "Distributor" before the 3 groups of variable the analysis is being calculated on. My plan was to do all of this in Power Query and then simply import the data model into Power BI for final reporting. After viewing a bunch of training videos and reading the Q&As on this site, it seems like it’s a better and more efficient idea to do this analysis in DAX either in Power BI or Power Pivot. I thought DAX was going to be much easier than it has turned out to be. For the life of me, I cannot figure out how to translate: filter($x$1:$x$300,((($c$1:$c$300=a1)*($d$1:$d$300=b1))) or if(a2=a1,if(b2=b1,c2+c1,c1),c1)). I can come up with the correct result for one, but if I try to filter or nest 2 non-date variables, I only get errors.

So here are my Qs:
1) Which way would be more efficient? Doing everything in Power Query, doing the calculations in Power Pivot (DAX) or Power BI (DAX)?

2) How does one calculate the % Running Total and then 80 Analysis in each using the most efficient method?

3) Is there a way to simply import the results of calculations done on a pivot table directly into the power pivot data model (or Power Query)? This would be the simplest route for me, and while that seems like something that definitely should be possible, I cannot find anything anywhere that shows how this is done.

a quick note. The formula I am using to calculate whether a variable in the top 80% is to compare the %running total of a row to the % total that is closest to 80%. Except in those instances where I am only assessing one variable (e.g. Customer), the % total closest to 80% is filtered by the remaining variables (e.g. Channel, Product).

I greatly appreciate any suggestions you may have.

POWER BI HELPER.xlsx
ABCDEFGHIJKLMNO
1
2NEED DAXNEED DAXFOR CONSIDERATION:
3FROM POWER QUERYPIVOT TABLECALCULATED TABLEELEMENTDB COUNT
4CHANNELPRODUCTCUSTOMERAVG VOLCHANNELPRODUCTCUSTOMERSum of AVG VOL% RUNNING TTL80 ANALYSISCHANNEL73
5CHANNEL 1PRODUCT 1CUSTOMER 1574CHANNEL 3PRODUCT 1CUSTOMER 1186435.06%TRUEPRODUCT35
6CHANNEL 1PRODUCT 1CUSTOMER 21527CHANNEL 3PRODUCT 1CUSTOMER 3162565.62%TRUECUSTOMER10,548
7CHANNEL 1PRODUCT 1CUSTOMER 3796CHANNEL 3PRODUCT 1CUSTOMER 4110586.40%TRUECPC64,563
8CHANNEL 1PRODUCT 2CUSTOMER 11801CHANNEL 3PRODUCT 1CUSTOMER 2723100.00%FALSECHAN/PROD2,518
9CHANNEL 1PRODUCT 2CUSTOMER 21787CHANNEL 3PRODUCT 2CUSTOMER 2184269.56%TRUE
10CHANNEL 1PRODUCT 2CUSTOMER 31611CHANNEL 3PRODUCT 2CUSTOMER 1806100.00%FALSE
11CHANNEL 2PRODUCT 1CUSTOMER 41477CHANNEL 3PRODUCT 13CUSTOMER 4111362.21%TRUE
12CHANNEL 2PRODUCT 5CUSTOMER 51799CHANNEL 3PRODUCT 13CUSTOMER 3676100.00%FALSE
13CHANNEL 2PRODUCT 7CUSTOMER 61897CHANNEL 1PRODUCT 2CUSTOMER 1180134.64%TRUE
14CHANNEL 3PRODUCT 2CUSTOMER 1806CHANNEL 1PRODUCT 2CUSTOMER 2178769.01%TRUE
15CHANNEL 3PRODUCT 2CUSTOMER 21842CHANNEL 1PRODUCT 2CUSTOMER 31611100.00%FALSE
16CHANNEL 3PRODUCT 13CUSTOMER 3676CHANNEL 1PRODUCT 1CUSTOMER 2152752.71%TRUE
17CHANNEL 3PRODUCT 13CUSTOMER 41113CHANNEL 1PRODUCT 1CUSTOMER 379680.19%TRUE
18CHANNEL 3PRODUCT 1CUSTOMER 11864CHANNEL 1PRODUCT 1CUSTOMER 1574100.00%FALSE
19CHANNEL 3PRODUCT 1CUSTOMER 2723CHANNEL 2PRODUCT 7CUSTOMER 61897100.00%TRUE
20CHANNEL 3PRODUCT 1CUSTOMER 31625CHANNEL 2PRODUCT 5CUSTOMER 51799100.00%TRUE
21CHANNEL 3PRODUCT 1CUSTOMER 41105CHANNEL 2PRODUCT 1CUSTOMER 41477100.00%TRUE
22
23NEED DAXNEED DAX
24FROM POWER QUERYCHAN+PROD PIVOT TABLECALCULATED TABLE
25CHANNELPRODUCTAVG VOLCHANNELPRODUCTSum of AVG VOL% RUNNING TTL80 ANALYSIS
26CHANNEL 1PRODUCT 12897CHANNEL 3PRODUCT 1531754.51%TRUE
27CHANNEL 1PRODUCT 25199CHANNEL 3PRODUCT 2264881.66%TRUE
28CHANNEL 2PRODUCT 11477CHANNEL 3PRODUCT 131,789.00100.00%FALSE
29CHANNEL 2PRODUCT 51799CHANNEL 1PRODUCT 25,199.0064.22%TRUE
30CHANNEL 2PRODUCT 71897CHANNEL 1PRODUCT 12,897.00100.00%FALSE
31CHANNEL 3PRODUCT 22648CHANNEL 2PRODUCT 71,897.0036.67%TRUE
32CHANNEL 3PRODUCT 131789CHANNEL 2PRODUCT 51,799.0071.45%TRUE
33CHANNEL 3PRODUCT 15317CHANNEL 2PRODUCT 11,477.00100.00%FALSE
34
35
36FROM POWER QUERYNEED DAXNEED DAX
37CUSTOMERAVG VOLCUST ONLY PIVOT TABLECALCULATED TABLE
38CUSTOMER 15877CUSTOMERSum of AVG VOL% RUNNING TTL80 ANALYSIS
39CUSTOMER 23236CUSTOMER 1587728.36%TRUE
40CUSTOMER 34768CUSTOMER 4522453.57%TRUE
41CUSTOMER 45224CUSTOMER 34,768.0076.57%TRUE
42CUSTOMER 5742CUSTOMER 23,236.0092.19%FALSE
43CUSTOMER 6877CUSTOMER 6877.0096.42%FALSE
44CUSTOMER 5742.00100.00%FALSE
CPC
Cell Formulas
RangeFormula
L5:L21L5=K5-0.8<=MIN(ABS(FILTER($K$5:$K$21,($G$5:$G$21=G5)*($H$5:$H$21=H5))-0.8))
K26:K33K26=$J26-0.8<=MIN(ABS(FILTER($J$26:$J$33,($G$26:$G$33=$G26))-0.8))
J39:J44J39=$I39-0.8<=MIN(ABS($I$39:$I$44-0.8))
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Not sure how to close this so I'm just making a comment, and giving it a check
 
Upvote 0
Not sure how to close this so I'm just making a comment, and giving it a check
FYI: The check mark on the right is to mark a solution to, not a closure of the thread, so I have removed your check-mark. We don't mark threads as closed in these forums, but your comment above is sufficient to indicate that you no longer require help.
 
Upvote 0
FYI: The check mark on the right is to mark a solution to, not a closure of the thread, so I have removed your check-mark. We don't mark threads as closed in these forums, but your comment above is sufficient to indicate that you no longer require help.
Thanks Peter. I never actually figured this out. I just rolled with Power Query. Had to add several several additional steps to get it to work though. I just thought after +50 views without anyone commenting, it was best to just close it. This is a great group of folks, and have helped me out of several jams. Didn't want you and the other experts to think I wasn't grateful, or feel like I was asking too much.

thanks again.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top