Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gluizqueiroz
Resolver I
Resolver I

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
Greg_Deckler
Super User
Super User

What about this Quick Measure?

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

What about this Quick Measure?

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hey @Greg_Deckler!

Thanks for too much help, your solution works perfectly! 

 

Thanks again! Have a nice day man.

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!

Sure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.