cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessicarocha
Helper II
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

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.

1.png

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.

2.png

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.

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@jessicarocha , Try like

 

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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. 

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.

1.png

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.

2.png

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.

 

View solution in original post

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%



jessicarocha_0-1631087686943.png

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!