Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |