Help with ABC Customer categorizing - DAX

[Okazakipedro]

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

[pbiuseruk]

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]

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]

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

[Okazakipedro]

Hello, the issue here is that the columns are A,B,C And the Measure is bringing the profitability, I can't do a switch case...

VAR SalesByProduct =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( SampleData, SampleData[Controlling Customer Code] ),

            "@ProdSales", [measure_job_totalProfitEur]

        ),

        ALLSELECTED ( SampleData )

    )

This is what is bringing the profitability.

[pbiuseruk]

It's hard for me to visualise this but if you share a PBIX file with some dummy data in there, I can take a look.

[Okazakipedro]

@pbiuseruk 

Absolutely, attached a sample file:

https://drive.google.com/file/d/1J_XdL6Zi1Z5x0aqBIr54lYvuUrX32Xpg/view?usp=sharing

[Ashish_Mathur]

Hi,

Given the buckets that you have in the support category table, 1,652 should fall in Category C (total of shipment number 1098 and 1250).  Shipment number 1167 does not fall in any category/bucket.  This is the measure i wrote

category_abcProfitEurr v1 = CALCULATE([measure_job_totalProfitEur],FILTER(ADDCOLUMNS(VALUES(SampleData[ShipmentNo]),"ABCD",CALCULATE([measure_job_totalRevenueEur],CALCULATETABLE(VALUES(SampleData[ShipmentID]))),"EFGH",CALCULATE([measure_job_totalProfitEur],CALCULATETABLE(VALUES(SampleData[ShipmentID])))),COUNTROWS(FILTER(Support_Category,[ABCD] >= Support_Category[RevenueLowerBoundaries] && [ABCD] < Support_Category[RevenueUpperBoundaries]&&[EFGH]>=Support_Category[ProfitLowerBoundaries]&&[EFGH]<Support_Category[ProfitUpperBoundaries])) >0))

[Okazakipedro]

@Ashish_Mathur  thanks but you see that the same value appears for A and B? this is the behaviour that I do not want, I would like it to show only A.

[Ashish_Mathur]

You are welcome.  My formula will work absolutely fine once you correct categories table.

[pbiuseruk]

Could you try using this:

NewMeasure = 
Var ControllingCustomerCode = SELECTEDVALUE(SampleData[Controlling Customer Code])
RETURN
CALCULATE(LASTNONBLANK ( Support_Category[Category], 1 ),
                FILTER (
                    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] )
                ), SampleData[Controlling Customer Code] = ControllingCustomerCode
)

Then make a table with the company code, the measure for the euro amount and this new measure. 

Please let me know if this is what you were after?

[Okazakipedro]

No, this is bringing the category A B and C instead of the profit

[pbiuseruk · Accepted solution]

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: