Forum Discussion
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):
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):
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:
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.
What about this Quick Measure?
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146
5 Replies
- Greg_DecklerCommunity Champion
What about this Quick Measure?
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146
- gluizqueirozResolver I
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!- Greg_DecklerCommunity Champion
Sure.
- gluizqueirozResolver I
Hey Greg_Deckler!
Thanks for too much help, your solution works perfectly!Thanks again! Have a nice day man.