Forum Discussion

Okazakipedro's avatar
Okazakipedro
Helper I
1 year ago
Solved

Help with ABC Customer categorizing - DAX

Hello dear Desktop users, good morning.

 

I am stuck in a DAX measure related to ABC Customer Segmentation. My current formula is as below, unfortunately the result brings 2 categories (A and B) I would like it to return always the top one, A => B => C... but had trouble, could anyone help me?

 

VAR SalesByProduct =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( SampleData, SampleData[Controlling Customer Code] ),
            "@ProdSales", [measure_job_totalProfitEur]
        ),
        ALLSELECTED ( SampleData )
    )
VAR AllSales =
    CALCULATE (
        [measure_job_totalProfitEur],
        ALLSELECTED ( SampleData )
    )
VAR ProductsInClass =
    FILTER (
        CROSSJOIN (
            SalesByProduct,
            'Support_Category'
        ),
            ([measure_job_totalProfitEur] > 'Support_Category'[ProfitLowerBoundaries]
            && [measure_job_totalProfitEur] <= 'Support_Category'[ProfitUpperBoundaries])
            ||
            ([measure_job_totalRevenueEur] > 'Support_Category'[RevenueLowerBoundaries]
            && [measure_job_totalRevenueEur] <= 'Support_Category'[RevenueUpperBoundaries])

 

        )
    VAR Result =
    CALCULATE (
        [measure_job_totalProfitEur],
        KEEPFILTERS ( ProductsInClass )
    )
RETURN
    Result
 
example for my Fact Table containing each shipment revenue and profit
 

rules (upper and lower boundries) to categorize the customer

 

 

Unfortunately, my measure is bringing 2 categories, it should only be a....

 

 

Need Help
  • pbiuseruk's avatar
    pbiuseruk
    1 year ago

    Slightly confused what you're expecting (please explain) - this is the result I get when I add your existing measure with the new measure I made:

     

12 Replies

  • pbiuseruk's avatar
    pbiuseruk
    Resolver IV
    1 year ago

    Hi,

    I'd recommend making calculated tables along the way so that you can see the outputs (e.g. output Products in class as a calculated table and make sure it's what you're expecting). Then it'll be much easier to see where it's going wrong.

    • Okazakipedro's avatar
      Okazakipedro
      Helper I
      1 year ago

      Hello pbiuseruk  good afternoon.


      Yes, I tried that and know that the issue is in the OR clause (since based on profitability it would fall in one category and based on revenue on another)... The issue is that I could not find a way to return only one category and prioritize the upper one (A->B-> C)...

      Thank you for the input 🙂

      • pbiuseruk's avatar
        pbiuseruk
        Resolver IV
        1 year ago

        If you can see there's multiple values being returned in the table and you only want one based on precedence, then you'd need to do an if or switch condition in the Result variable. e.g. If category A exists, then take value A, if Category B exists then take value B, etc...