ABC Measure Counting with Keeping the Filters

[schlammi]

Hello,

I have an ABC Measure. It is working fine and I understand what it does:

Product ABCD = 
VAR TotalSales =
    CALCULATE (
        SUM ( FactOrderLine[price_totalPrice] ),
        ALLSELECTED ( FactOrderLine )
    )
VAR CurrentSales =
    SUM ( FactOrderLine[price_totalPrice] )
VAR SummarizedTable =
    SUMMARIZE (
        ALLSELECTED ( FactOrderLine ),
        DimProduct[name],
        "Total Sales", SUM ( FactOrderLine[price_totalPrice] )
    )
VAR CumulativeSum =
    SUMX (
        FILTER ( SummarizedTable, [Total Sales] >= CurrentSales ),
        [Total Sales]
    )
RETURN
IF(CumulativeSum/TotalSales<=0.5,"A",IF(CumulativeSum/TotalSales<=0.8,"B",IF(CumulativeSum/TotalSales<=0.95,"C","D")))

Now I want to calculate how many A-Products there, B-Products and so on.

Also the filter in the filter context should apply on this count.

I tried to create a new table where I have all the products with the ABC Category:

ABCD Table = 
    SUMMARIZE(DimProduct,DimProduct[name],"ABCD",[Product ABCD])
  

Then I created a second table, which count the first table. I think I have to create two tables, because I can not really store a table in a variable and refer to it. Is this right?

ABCD Table Count = 

VAR SummarizedTable = 
    SUMMARIZECOLUMNS (
        'ABCD Table'[ABCD],
        "Anzahl", COUNTROWS ( 'ABCD Table' )
    )
RETURN
    SummarizedTable

Then I get the result, I would like to get. But no filter get apply. I figured out, that the created table get calculated before the filter are getting applied. So there is now way to do this with calculated tables. Is this right? Which other solutions I can do?

I found some similar topics but no topic really brought me a solution which worked for me.

File: File 

Thanks a lot for helping.

Greetings

Yannick

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[DataNinja777]

Hi Yannik,

Would you be able to share the pbix file for this task?  I'd like to have a look at it.  

[schlammi]

Hello Sakiko,

I ve uploaded a testfile to my first post. Look at the bottom of the Messages.

Cheers

[DataNinja777]

Hi @schlammi

Thank you for the file link. I downloaded it and had a look at it, and have a couple of questions to clarify.  

1. Data model itself is quite simple, but one fact table and two dimension tables, but I noted that although DimDate table has a one-to-many relationship with the fact table, it is not utilized in the CumulativeSum calculation.  In the CumulativeSum variable calculation what are you trying to achieve? Are you trying to accumulate with respect to the time dimension, or product dimension?  If it is cumulative over time, why is DimDate table not utilized to do the cumulation over time in more standard manner?  

2. I noticed that SummarizedTable variable included in the measure is just like below which can be easily obtained by the relationship between the product dimension table and the fact table. But is there any reason why this SummarizedTable variable was created?  

Also, dax measure is generating the error message "'SummarizedTable' is a table name and cannot be used to define a variable."

My feeling is that this measure looks overly complicated given the tasks you are trying to accomplish.  I'd greatly appreciate if you could instruct me what you are trying to do (like for example what you are trying to accumulate, i.e., time dimension or product dimension), etc. 

Thanks for your further clarification of this task. 

[schlammi]

Hello,

I ve created a new file from my actual file which has data, which I can not share.

1. No, the cummulative Sum describes the Sales Amount over the Product. In this visual for example I calculated the cummulative Sum divided by the total Sales Amount ( the orange line):

x-axis has all product names. This works good.

2. I dont get this error message. I ve created this message with the help of a youtube video. I ve understood how it s working. You can tell me an easier solution.

The goal of the measure is to figure out which products cause big Sales. So A Category Products are all Products up to 50% of the Sales.

But this measure is working, maybe a bit too complicated, but it s working. I just dont know how to count, how many A categories are there.

Greetings

Yannick

[schlammi]

Chat GPT actually helped me. I have to create 4 Measures, similar to my ABC measure:

A Products: Count of A Products = 
VAR TotalSales =
    CALCULATE (
        SUM ( FactOrderLine[price_totalPrice] ),
        ALLSELECTED ( FactOrderLine )
    )
VAR CurrentSales =
    SUM ( FactOrderLine[price_totalPrice] )
VAR SummarizedTable =
    SUMMARIZE (
        ALLSELECTED ( FactOrderLine ),
        DimProduct[name],
        "Total Sales", SUM ( FactOrderLine[price_totalPrice] )
    )
VAR CumulativeSum =
    SUMX (
        FILTER ( SummarizedTable, [Total Sales] >= CurrentSales ),
        [Total Sales]
    )
VAR ABCCategory = 
    IF(CumulativeSum/TotalSales<=0.5,"A",IF(CumulativeSum/TotalSales<=0.8,"B",IF(CumulativeSum/TotalSales<=0.95,"C","D")))
RETURN
    COUNTROWS(
        FILTER(ALLSELECTED(DimProduct), [Product ABCD] = "A")
    )