Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
))
Solved! Go to 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.
Please download the attachment to check more details.
Best Regards,
Angelia
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
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.
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
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!
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.
Please download the attachment to check more details.
Best Regards,
Angelia
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |