Forum Discussion

gluizqueiroz's avatar
gluizqueiroz
Resolver I
7 years ago
Solved

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. 

5 Replies