ABC Measure Counting with Keeping the Filters

Question

Hello,I have an ABC Measure. It is working fine and I understand what it does:&nbsp;&nbsp;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] &gt;= CurrentSales ),
        [Total Sales]
    )
RETURN
IF(CumulativeSum/TotalSales&lt;=0.5,"A",IF(CumulativeSum/TotalSales&lt;=0.8,"B",IF(CumulativeSum/TotalSales&lt;=0.95,"C","D")))&nbsp;&nbsp;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.&nbsp;I tried to create a new table where I have all the products with the ABC Category:&nbsp;ABCD Table = 
    SUMMARIZE(DimProduct,DimProduct[name],"ABCD",[Product ABCD])
  &nbsp;&nbsp;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?&nbsp;ABCD Table Count =

VAR SummarizedTable = 
    SUMMARIZECOLUMNS (
        'ABCD Table'[ABCD],
        "Anzahl", COUNTROWS ( 'ABCD Table' )
    )
RETURN
    SummarizedTable&nbsp;&nbsp;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?&nbsp;I found some similar topics but no topic really brought me a solution which worked for me.File:&nbsp;File&nbsp;Thanks a lot for helping.&nbsp;Greetings&nbsp;Yannick

dataninja777 · Answer

Hi Yannik,Would you be able to share the pbix file for this task?&nbsp; I'd like to have a look at it.&nbsp;&nbsp;

schlammi · Answer

Hello Sakiko,&nbsp;I ve uploaded a testfile to my first post. Look at the bottom of the Messages.&nbsp;Cheers

dataninja777 · Answer

Hi @schlammi&nbsp;Thank you for the file link. I downloaded it and had a look at it, and have a couple of questions to clarify.&nbsp;&nbsp;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.&nbsp; In the CumulativeSum variable calculation what are you trying to achieve? Are you trying to accumulate with&nbsp;respect to the time dimension, or product dimension?&nbsp; If it is cumulative over time, why is DimDate table not utilized to do the cumulation over time in more standard manner?&nbsp;&nbsp;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?&nbsp;&nbsp;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.&nbsp; 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.&nbsp;Thanks for your further clarification of this task.&nbsp;

schlammi · Answer

Hello,&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Greetings&nbsp;Yannick

schlammi · Answer

Chat GPT actually helped me. I have to create 4 Measures, similar to my ABC measure:&nbsp;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] &gt;= CurrentSales ),
        [Total Sales]
    )
VAR ABCCategory = 
    IF(CumulativeSum/TotalSales&lt;=0.5,"A",IF(CumulativeSum/TotalSales&lt;=0.8,"B",IF(CumulativeSum/TotalSales&lt;=0.95,"C","D")))
RETURN
    COUNTROWS(
        FILTER(ALLSELECTED(DimProduct), [Product ABCD] = "A")
    )

Forum Discussion

ABC Measure Counting with Keeping the Filters

6 Replies

Related content

Count Measure

Total Min/Max measure where and keep the value static

Counting nulls

Create count measure

SELECTEDVALUE measure from slicer keeps returning blank value when used on another measure