cancel
Showing results for
Did you mean:  Helper II

## Calculate percentage of a measure total

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 ])

Cumulative Total =
IF(
NOT(ISBLANK([Total Sales])),
CALCULATE(
[Total Sales],
FILTER(
ALL(Tabelle1[Product]),
SUM(Tabelle1[Sales ])<= [Total Sales]
)
)
)

Now, I am having trouble creating the percentage of the cumulative total measure. I would like to have the Cumulative Total per row divided by the sum of the cumulative total for all products. Thus, I tried this formula:
% Cumulative Total = [Cumulative Total] / CALCULATE([Cumulative Total], ALL(Tabelle1))

However, I obtain a weird value. For every row in the visualisation table, thus, for every product, I get an "infinity" value.

Here is what my data looks like when I try to visualise in a table:

 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

Can you help me to fix it?
1 ACCEPTED SOLUTION  Community Support

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.

4 REPLIES 4  Super User

@jessicarocha , Try like

Cumulative Total =
IF(
NOT(ISBLANK([Total Sales])),
CALCULATE(
[Total Sales],
FILTER(
ALL(Tabelle1[Product]),
SUM(Tabelle1[Product])<= [Product]
)
)
)

Proud to be a Super User!  Helper II

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

DIVIDE (
'Product'[Cumulated Sales],
SUM ( 'Product'[Product Sales] )
)

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.  Community Support

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.  Helper II

Hi @RicoZhou . 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%   