Calculate percentage of a measure total

[jessicarocha]

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?

[amitchandak]

@jessicarocha , Try like

Cumulative Total =

IF(

     NOT(ISBLANK([Total Sales])),

      CALCULATE(

               [Total Sales],

               FILTER(

                           ALL(Tabelle1[Product]),

                            SUM(Tabelle1[Product])<= [Product]

                   )

           )

  )

[jessicarocha]

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. 

[v-rzhou-msft · Accepted solution]

Hi @jessicarocha 

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]

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%