cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

How to calculate acumulated values without a visual table using pure measures to ABC Analysis

I'm trying to create a ABC Analysis, yes, I already tested the ABC Analysis by DAX Patterns and it is to slow for my use case.


I have the following table (For example, the table has more values than that):

 

image.png

I created some measures to acumulate % about sales over total sales, following measures:

 

TotalSales = SUM(Fact_Sales[Value_Sales])

TotalSalesStatic = CALCULATE([TotalSales]; ALL(Fact_Sales))

Percent = DIVIDE([TotalSales]; [TotalSalesStatic]) 

AcumulatedValue = CALCULATE([TotalSales]; TOPN(RANKX(ALL(Fact_Sales); [TotalSales]); ALL(Fact_Sales[Id_Product]); [TotalSales]))

%AcumulatedValue = DIVIDE(Fact_Sales[AcumulatedValue]; [TotalSalesStatic])

Classification = 
    IF([%AcumulatedValue] < 0,7; "A"; 
        IF([%AcumulatedValue] >= 0,7 && [%AcumulatedValue] < 0,9; "B";
            IF([%AcumulatedValue] >= 0,9; "C")
        )
    )

When I put this measures on a visual table, I have the following result (For example, the table has more rows than that, including Classification B and C):

image.png

 

As you can see, the column [%AcumulatedValue] is working nice and the Classification too, my problem is when I wanna put a "SUM of %AcumulatedValue" where Classification = A on a card, for example.
In other words, I wanna can extract the values where Classification is A or B or C. I wanna do this to can do the following wonderful measures:

SalesOfClassificationA = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "A") 

SalesOfClassificationB = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "B") 

SalesOfClassificationC = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "C")


If I make this measure and put it on a card, I have the following result:

 

image.png

This is not a SUM Sales of Classification A for true, this a total SUM os Sales, not especific Classification A. 
I wanna SUM the Sales, SUM the Cost of Produtos, Calculate the Profit and show to user the [%AcumulatedValue] for products where the Classification = "A" or "B" or "C" in a CARDS, not in a VISUAL TABLE

Is it possible to do with pure measures? I need it totally dynamic. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

What about this Quick Measure?

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


5 REPLIES 5
Super User
Super User

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

What about this Quick Measure?

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

Hey @Greg_Deckler.
Better than I expected!

I'm trying to adapt your quick mesure to my personal case, if I had some problems, can I ask you?

Thanks!

Super User
Super User

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

Sure.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

Hey @Greg_Deckler!

Thanks for too much help, your solution works perfectly! 

 

Thanks again! Have a nice day man.

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

Hey @Greg_Deckler.

I've been working on the project ABC Analysis and i have a problem on a specific part. 
My problem is:
I have 5 slicers on my project, but only 3 slicers should affect the calculate of ABC, how I can "ignore" this slicers?
I tried ALLEXCEPT, and it worked, but the project get too slow, without ALLEXCEPT (using ALLSELECTED) the project goes fast. 

 

 

mABC Class = 
VAR __salesTable = ADDCOLUMNS(ALLSELECTED(BI_ABCAnalysis);"__TotalSale";BI_ABCAnalysis[Value_Sale])
VAR __salesTable1 = GROUPBY(__salesTable;BI_ABCAnalysis[Id_Product];"__ProductSales";SUMX(CURRENTGROUP();[__TotalSale]))
VAR __salesTable2 = ADDCOLUMNS(__salesTable1;"__CumulatedSales"; SUMX(FILTER(__salesTable1;[__ProductSales]>=EARLIER([__ProductSales]));[__ProductSales]))
VAR __totalProductSales = SUMX(__salesTable1;[__ProductSales])
VAR __salesTable3 = ADDCOLUMNS(__salesTable2;"__CumulatedPercentage"; DIVIDE([__CumulatedSales];__totalProductSales;0))
VAR __salesTable4 = ADDCOLUMNS(__salesTable3;"__ABC Class"; SWITCH(TRUE();[__CumulatedPercentage]<=0,7;"A";[__CumulatedPercentage]<=0,9;"B";"C"))
VAR __salesTable5 = FILTER(__salesTable4;BI_ABCAnalysis[Id_Product] = MAX(BI_ABCAnalysis[Id_Product]))
RETURN MAXX(__salesTable5;[__ABC Class])

I change the first line:
FROM THIS:

 

VAR __salesTable = ADDCOLUMNS(ALLSELECTED(BI_ABCAnalysis);"__TotalSale";BI_ABCAnalysis[Value_Sale])

TO THIS:

VAR __salesTable = ADDCOLUMNS(ALLEXCEPT(BI_ABCAnalysis; BI_ABCAnalysis[Group]; BI_ABCAnalysis[Subgroup]; BI_ABCAnalysis[Product]);"__TotalSale";BI_ABCAnalysis[Value_Sale])

Currently my project have near 122 thousand rows and in the course of time, my project goes bigger than now.