Pareto Chart with multiple aggregation levels

[ce2016]

Dear all,

I am trying to create a pareto chart that allows me to visualize the amount of deliveries categorized by different codes, whereas I am able to switch on slicer filter from year, to months, to calendar week.

The data source is one huge excel file with data on very granular level.

So far I succeeded in getting a correct ranking, which is neccessary to cumulate the numbers.

However, only the cumulation on the most granular level is correct and I think it has something to do with this ALLEXPECT function. If I remove let's say Calendar Week (Calendar Year/Week) from the ALLEXPECT statement then the cumulation on monthly level is correct, but overall year and weekly is incorrect.

Do you have any ideas how I could fix the code?

DC Analysis is the name of the table, Delivery Code labels the respective delivery (direct, indirect, express,...)

Ranking = RANKX(ALL('DC Analysis'[Delivery Code]);
			CALCULATE(SUM('DC Analysis'[Amount of Deliveries]);
			ALLEXCEPT('DC Analysis';'DC Analysis'[Delivery Code];'DC Analysis'[Calendar Year/Month];'DC Analysis'[Calendar Year/Week])
							);;DESC)


Cumulative Total = CALCULATE(
 SUM('DC Analysis'[Amount of Deliveries]);
 FILTER(ALLSELECTED('DC Analysis');
 [Ranking]<= MAXX('DC Analysis';[Ranking])
 ))

[v-huizhn-msft]

Hi @ce2016,

I think you should rank all your rows based on each day. Use the Calendar Year/Day in ALLEXCEPT statement, and check if it works fine.

In addition, do you SUM('DC Analysis'[Amount of Deliveries]) in ranking calculated column? Could you please share your sample table model with dummy data for further analysis.

Best Regards,
Angelia

[ce2016]

Hey Angelina @v-huizhn-msft, thanks for helping me out.

I put the Calendar Year / Day in the Allexpect ranking and removed all other ones but now the ranking is all wrong.

SUM('DC Analysis'[Amount of Deliveries]) is done as a measure. As column I did get a wrong ranking.

This is what the underlying table looks like (this is just an excerpt)

Calendar Year/Month	Calendar Year/Week	Actual delivery date	Delivery Code	Cust.Hier.:Level 3	Cust.Hier.:Level 4	Cust.Hier.:Level 5	Amount of Delivery
09.2017	39.2017	25.09.2017	AB	L3 Customer A	L4 Customer A	L5 Customer A	15555
09.2017	39.2017	25.09.2017	CD	L3 Customer A	L4 Customer A	L5 Customer A	6000
09.2017	39.2017	25.09.2017	EF	L3Customer B	L4 Customer B	L5 Customer B	54666
09.2017	39.2017	25.09.2017	GH	L3 Customer A	L4 Customer A	L5 Customer A	8036
09.2017	39.2017	25.09.2017	CA	L3 Customer B	L4 Customer B	L5 Customer A	7110
09.2017	39.2017	25.09.2017	AB	L3 Customer A	L4 Customer A	L5 Customer A	5022
09.2017	39.2017	25.09.2017	AB	L3 Customer A	L4 Customer A	L5 Customer A	200
09.2017	39.2017	25.09.2017	DD	L3 Customer A	L4 Customer B	L5 Customer B	300
09.2017	39.2017	25.09.2017	CD	L3 Customer C	L4 Customer C	L5 Customer C	400
09.2017	39.2017	26.09.2017	EF	L3 Customer A	L4 Customer A	L5 Customer A	1152
09.2017	39.2017	27.09.2017	CA	L3 Customer A	L4 Customer A	L5 Customer A	1152

This what the graph and figures look like if the ALLEXCPECT statement includes the items Delivery Code, Calendar Year/Monthly and Calendar Year/Weekly, whereas the filter is set on a specific week.

This what the graph and figures look like with the same ALLEXCPECT statement when the filter is set on a month.

[v-huizhn-msft]

Hi @ce2016,

Do you mind share your .pbix file for further analysis? It's hard to find the specific reason by the screenshot you shared.

Best Regards,
Angelia

[ce2016]

Hey Angelina,

I uploaded a sample pbix. file on dropbox:

https://www.dropbox.com/s/kpdoqlrtc32egnt/Pareto%20Chart%20Sample.pbix?dl=0

Thank you for your great support!

[v-huizhn-msft · Accepted solution]

Hi @ce2016,

After check in details, I get expected results eventually. The "MAXX('Sample',[RankingSample])" in your formula is wrong, you can create another measure to test, I create another measure named "Test max of RankingSample", it should returen the max value utill current rows, while it returns wrong result, so please correct using VAR in DAX, like [current max Measure] measure, then you can use it in your cumulative total measure.

Test max of RankingSample = MAXX('Sample',[RankingSample])
current max Measure = VAR rr=[RankingSample]
RETURN(rr)

Then change your formula as follows,

New Cumulative TotalSample = VAR rr=[RankingSample]
RETURN( CALCULATE(
	SUM('Sample'[Amount of Deliveries]),
	FILTER(ALLSELECTED('Sample'),
		[RankingSample]<= rr
	)))

Cumulated Percentage = [New Cumulative TotalSample]/[Total per DC]

Now when you can get expected result whether the filter is set on month or week.

filter on monthfilter on week

Please download the attachment to check more details.

Best Regards,
Angelia

[ce2016]

Hi Angelina @v-huizhn-msft, I just noticed that the cumulated amount of deliveries is not correct if I remove the filters or select on monthly level. Only when the filters are set on a specific calendar week, the values are correct...Can you check again please?

[rcyber]

Hi all!

I'm trying to create the similar report.

I put formulas as Angelina wrote above.

But I have strange results.

Cumulative total amount is wrong. I don't understand why it happens.

My pbix file is here: link

Can anybody help me?

Thanks in advance,

Alex.