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.
My end goal is to calculate an ABC classification for the products. To start, I need a cumulative total and then a percentage of a cumulative total. I am following the steps suggested at ABC classification – DAX Patterns but converting everything to measure because I do not have access to power query or calculated columns due to how the connection to the database is done in our company.
An dummy example of my dataset is:
Product | Sales | Production starting date | ||
Product 01 | 100 | 01.08.2019 | ||
Product 02 | 150 | 02.08.2020 | ||
Product 03 | 80 | 03.10.2018 | ||
Product 04 | 90 | 04.08.2020 | ||
Product 05 | 20 | 05.08.2020 | ||
Product 06 | 30 | 06.08.2020 | ||
Product 07 | 469 | 07.09.2020 | ||
Product 08 | 210 | 08.08.2020 | ||
Product 09 | 26 | 09.08.2020 | ||
Product 10 | 45 | 10.08.2020 | ||
Product 11 | ||||
Product 12 | 269 | 12.08.2020 | ||
Procuct 13 | 13.08.2020 | |||
Product 14 | 108 | 14.08.2021 | ||
Product 15 | 190 | 15.08.2021 |
Thus, I created the following measures:
Total Sales = sum(Tabelle1[Sales ])
Product | Sales | Cumulative Total | % Cumulative Total | |||
Product 07 | 469 | 469 | Infinity | |||
Product 12 | 269 | 738 | Infinity | |||
Product 08 | 210 | 948 | Infinity | |||
Product 15 | 190 | 1138 | Infinity | |||
Product 02 | 150 | 1288 | Infinity | |||
Product 14 | 108 | 1396 | Infinity | |||
Product 01 | 100 | 1496 | Infinity | |||
Product 04 | 90 | 1586 | Infinity | |||
Product 03 | 80 | 1666 | Infinity | |||
Product 10 | 45 | 1711 | Infinity | |||
Product 06 | 30 | 1741 | Infinity | |||
Product 09 | 26 | 1767 | Infinity | |||
Procuct 05 | 20 | 1787 | Infinity |
Solved! Go to Solution.
I think there is something wrong in second measure.
CALCULATE([Cumulative Total], ALL(Tabelle1))
This code will return blank. In Power BI, blank is equal to 0.
So [Cumulative Total]/[Measure] will return Infinity. number/ 0 = Infinity.
[Cumulative Total] measure will return correct result, but it is not suitable to be used to get the whole Total.
To get the whole Total, you can try this code.
Measure = CALCULATE([Total Sales], ALL(Tabelle1))
So new [Cumulative Total %] will be as below.
% Cumulative Total = [Cumulative Total] / CALCULATE([Total Sales], ALL(Tabelle1))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jessicarocha , Try like
Hi @amitchandak ,
thank you for your answer. However, I get an error with this formula. I guess it has to do with the fact that the Product column is in text format, like: Product 01, ...Product 100. Thus, I can not do a sum here.
I will update my answer to include an dummy example of the dataset.
However, I don't have a problem with the Cumulative Total measure. This is working.
The problem appears when I try to create the Percentage of the cumulative total.
According to the link I am following ABC classification – DAX Patterns. I am trying to convert this calculated column suggested in the website
as a measure that works for me. For now I tried this formula (that I thought to be equivalent):
% Cumulative Total = [Cumulative Total] / CALCULATE([Cumulative Total], ALL(Tabelle1))
But I get "infinite" as a result.
I think there is something wrong in second measure.
CALCULATE([Cumulative Total], ALL(Tabelle1))
This code will return blank. In Power BI, blank is equal to 0.
So [Cumulative Total]/[Measure] will return Infinity. number/ 0 = Infinity.
[Cumulative Total] measure will return correct result, but it is not suitable to be used to get the whole Total.
To get the whole Total, you can try this code.
Measure = CALCULATE([Total Sales], ALL(Tabelle1))
So new [Cumulative Total %] will be as below.
% Cumulative Total = [Cumulative Total] / CALCULATE([Total Sales], ALL(Tabelle1))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft . Thank you so much! That is exactly what I asked for. I really appreciate the help!
I just realised I have a different problem with the Cumulative Total formula. When Sales values are the same, it does not sum it properly. Thus,, the % Cumulative Total remains the same for those cases.
Do you have any idea how to fix this as well?
Example: where it is marked in red should be:
Product | Sales | Cumulative Total | % Cumulative Total | |||
Product 01 | 100 | 1496 | 72.03% | |||
Product 16 | 100 | 1596 | 76.84% | |||
Product 17 | 100 | 1696 | 81.66% |
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |