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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2											NEED DAX	NEED DAX		FOR CONSIDERATION:
3		FROM POWER QUERY					PIVOT TABLE					CALCULATED TABLE		ELEMENT	DB COUNT
4		CHANNEL	PRODUCT	CUSTOMER	AVG VOL		CHANNEL	PRODUCT	CUSTOMER	Sum of AVG VOL	% RUNNING TTL	80 ANALYSIS		CHANNEL	73
5		CHANNEL 1	PRODUCT 1	CUSTOMER 1	574		CHANNEL 3	PRODUCT 1	CUSTOMER 1	1864	35.06%	TRUE		PRODUCT	35
6		CHANNEL 1	PRODUCT 1	CUSTOMER 2	1527		CHANNEL 3	PRODUCT 1	CUSTOMER 3	1625	65.62%	TRUE		CUSTOMER	10,548
7		CHANNEL 1	PRODUCT 1	CUSTOMER 3	796		CHANNEL 3	PRODUCT 1	CUSTOMER 4	1105	86.40%	TRUE		CPC	64,563
8		CHANNEL 1	PRODUCT 2	CUSTOMER 1	1801		CHANNEL 3	PRODUCT 1	CUSTOMER 2	723	100.00%	FALSE		CHAN/PROD	2,518
9		CHANNEL 1	PRODUCT 2	CUSTOMER 2	1787		CHANNEL 3	PRODUCT 2	CUSTOMER 2	1842	69.56%	TRUE
10		CHANNEL 1	PRODUCT 2	CUSTOMER 3	1611		CHANNEL 3	PRODUCT 2	CUSTOMER 1	806	100.00%	FALSE
11		CHANNEL 2	PRODUCT 1	CUSTOMER 4	1477		CHANNEL 3	PRODUCT 13	CUSTOMER 4	1113	62.21%	TRUE
12		CHANNEL 2	PRODUCT 5	CUSTOMER 5	1799		CHANNEL 3	PRODUCT 13	CUSTOMER 3	676	100.00%	FALSE
13		CHANNEL 2	PRODUCT 7	CUSTOMER 6	1897		CHANNEL 1	PRODUCT 2	CUSTOMER 1	1801	34.64%	TRUE
14		CHANNEL 3	PRODUCT 2	CUSTOMER 1	806		CHANNEL 1	PRODUCT 2	CUSTOMER 2	1787	69.01%	TRUE
15		CHANNEL 3	PRODUCT 2	CUSTOMER 2	1842		CHANNEL 1	PRODUCT 2	CUSTOMER 3	1611	100.00%	FALSE
16		CHANNEL 3	PRODUCT 13	CUSTOMER 3	676		CHANNEL 1	PRODUCT 1	CUSTOMER 2	1527	52.71%	TRUE
17		CHANNEL 3	PRODUCT 13	CUSTOMER 4	1113		CHANNEL 1	PRODUCT 1	CUSTOMER 3	796	80.19%	TRUE
18		CHANNEL 3	PRODUCT 1	CUSTOMER 1	1864		CHANNEL 1	PRODUCT 1	CUSTOMER 1	574	100.00%	FALSE
19		CHANNEL 3	PRODUCT 1	CUSTOMER 2	723		CHANNEL 2	PRODUCT 7	CUSTOMER 6	1897	100.00%	TRUE
20		CHANNEL 3	PRODUCT 1	CUSTOMER 3	1625		CHANNEL 2	PRODUCT 5	CUSTOMER 5	1799	100.00%	TRUE
21		CHANNEL 3	PRODUCT 1	CUSTOMER 4	1105		CHANNEL 2	PRODUCT 1	CUSTOMER 4	1477	100.00%	TRUE
22
23										NEED DAX	NEED DAX
24			FROM POWER QUERY				CHAN+PROD PIVOT TABLE				CALCULATED TABLE
25			CHANNEL	PRODUCT	AVG VOL		CHANNEL	PRODUCT	Sum of AVG VOL	% RUNNING TTL	80 ANALYSIS
26			CHANNEL 1	PRODUCT 1	2897		CHANNEL 3	PRODUCT 1	5317	54.51%	TRUE
27			CHANNEL 1	PRODUCT 2	5199		CHANNEL 3	PRODUCT 2	2648	81.66%	TRUE
28			CHANNEL 2	PRODUCT 1	1477		CHANNEL 3	PRODUCT 13	1,789.00	100.00%	FALSE
29			CHANNEL 2	PRODUCT 5	1799		CHANNEL 1	PRODUCT 2	5,199.00	64.22%	TRUE
30			CHANNEL 2	PRODUCT 7	1897		CHANNEL 1	PRODUCT 1	2,897.00	100.00%	FALSE
31			CHANNEL 3	PRODUCT 2	2648		CHANNEL 2	PRODUCT 7	1,897.00	36.67%	TRUE
32			CHANNEL 3	PRODUCT 13	1789		CHANNEL 2	PRODUCT 5	1,799.00	71.45%	TRUE
33			CHANNEL 3	PRODUCT 1	5317		CHANNEL 2	PRODUCT 1	1,477.00	100.00%	FALSE
34
35
36				FROM POWER QUERY					NEED DAX	NEED DAX
37				CUSTOMER	AVG VOL		CUST ONLY PIVOT TABLE			CALCULATED TABLE
38				CUSTOMER 1	5877		CUSTOMER	Sum of AVG VOL	% RUNNING TTL	80 ANALYSIS
39				CUSTOMER 2	3236		CUSTOMER 1	5877	28.36%	TRUE
40				CUSTOMER 3	4768		CUSTOMER 4	5224	53.57%	TRUE
41				CUSTOMER 4	5224		CUSTOMER 3	4,768.00	76.57%	TRUE
42				CUSTOMER 5	742		CUSTOMER 2	3,236.00	92.19%	FALSE
43				CUSTOMER 6	877		CUSTOMER 6	877.00	96.42%	FALSE
44							CUSTOMER 5	742.00	100.00%	FALSE
CPC

Cell Formulas
Range	Formula
L5:L21	L5	=K5-0.8<=MIN(ABS(FILTER($K$5:$K$21,($G$5:$G$21=G5)*($H$5:$H$21=H5))-0.8))
K26:K33	K26	=$J26-0.8<=MIN(ABS(FILTER($J$26:$J$33,($G$26:$G$33=$G26))-0.8))
J39:J44	J39	=$I39-0.8<=MIN(ABS($I$39:$I$44-0.8))

 

[RICH937]

Not sure how to close this so I'm just making a comment, and giving it a check

 

[Peter_SSs]

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.

 

[RICH937]

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.