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

     

Okazakipedro's avatar
Okazakipedro
Helper I
1 year ago

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's avatar
pbiuseruk
Resolver IV
1 year ago

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.

    • pbiuseruk's avatar
      pbiuseruk
      Resolver IV
      1 year ago

      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's avatar
        Okazakipedro
        Helper I
        1 year ago

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      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's avatar
        Okazakipedro
        Helper I
        1 year ago

        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.