cancel
Showing results for
Search instead for
Did you mean:
gluizqueiroz Member

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions 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

## 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!

gluizqueiroz Member

## 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

## 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!

gluizqueiroz Member

## 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.

gluizqueiroz Member

## 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.