ABC Catetorizing Measures, how to avoid bringing 2 categories for the same Customer (DAX)

[Okazakipedro]

Hello All, good morning.

I am stuck in a DAX measure related to ABC Customer Segmentation. My current formula is as below:

category_abcProfitEurr =

VAR SalesByProduct =

    CALCULATETABLE (

        ADDCOLUMNS (

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

            "@ProdSales", [job_totalProfitEur]

        ),

        ALLSELECTED ( Fact_JobFinancials )

    )

VAR AllSales =

    CALCULATE (

        [job_totalProfitEur],

        ALLSELECTED ( Fact_JobFinancials )

    )

VAR ProductsInClass =

    FILTER (

        CROSSJOIN (

            SalesByProduct,

            'Support_Category&Date'

        ),

            ([job_totalProfitEur] > 'Support_Category&Date'[ProfitLowerBoundaries]

            && [job_totalProfitEur] <= 'Support_Category&Date'[ProfitUpperBoundaries])

            ||

            ([job_totalRevenueEur] > 'Support_Category&Date'[RevenueLowerBoundaries]

            && [job_totalRevenueEur] <= 'Support_Category&Date'[RevenueUpperBoundaries])

        )

    VAR Result =

    CALCULATE (

        [job_totalProfitEur],

        KEEPFILTERS ( ProductsInClass )

    )

RETURN

    Result

Unfortunately this brings sometimes multiple categories for the same customer, such as 

I believe the issue is related to the or clause, for this customer, due to the revenue it falls in the A category, but due to profit in B... But I want to always return the highest one.

Could anyone help me?

[v-kongfanf-msft]

Hi @Okazakipedro ,

Did @bhanu_gautam reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[bhanu_gautam]

@Okazakipedro , Try using

category_abcProfitEurr =
VAR SalesByProduct =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Fact_JobFinancials, Fact_JobFinancials[Controlling Customer Code] ),
"@ProdSales", [job_totalProfitEur]
),
ALLSELECTED ( Fact_JobFinancials )
)
VAR AllSales =
CALCULATE (
[job_totalProfitEur],
ALLSELECTED ( Fact_JobFinancials )
)
VAR ProductsInClass =
FILTER (
CROSSJOIN (
SalesByProduct,
'Support_Category&Date'
),
([job_totalProfitEur] > 'Support_Category&Date'[ProfitLowerBoundaries]
&& [job_totalProfitEur] <= 'Support_Category&Date'[ProfitUpperBoundaries])
||
([job_totalRevenueEur] > 'Support_Category&Date'[RevenueLowerBoundaries]
&& [job_totalRevenueEur] <= 'Support_Category&Date'[RevenueUpperBoundaries])
)
VAR CategoryA =
CALCULATE (
[job_totalProfitEur],
KEEPFILTERS ( ProductsInClass ),
'Support_Category&Date'[Category] = "A"
)
VAR CategoryB =
CALCULATE (
[job_totalProfitEur],
KEEPFILTERS ( ProductsInClass ),
'Support_Category&Date'[Category] = "B"
)
VAR CategoryC =
CALCULATE (
[job_totalProfitEur],
KEEPFILTERS ( ProductsInClass ),
'Support_Category&Date'[Category] = "C"
)
VAR Result =
IF (
NOT ISBLANK(CategoryA), CategoryA,
IF (
NOT ISBLANK(CategoryB), CategoryB,
CategoryC
)
)
RETURN
Result

[Okazakipedro]

@bhanu_gautam  ID did not work, but I am attaching a file so you can see what is the problem, would really appreciate your inputs 🙂

-- Edit, it seems that I can't post the sample pbix, therefore I am writing everything here, sorry:

Fact Table (SampleData)

Shipment_Branch_Quarter_PK	ShipmentID	ShipmentNo	job_totalRevenueEur	job_totalProfitEur	Controlling Customer Code
STR-SSTR00001098-2024-Q1	543A78E1-18D7-467A-BECC-52C12018DBB2	SSTR00001098	6.880, €	756,27 €	ALAHLISAF
STR-SSTR00001167-2024-Q1	439B06A3-E0EF-4E2C-83E7-A0CED138D27E	SSTR00001167	0, €	0, €	ALAHLISAF
STR-SSTR00001167-2024-Q2	439B06A3-E0EF-4E2C-83E7-A0CED138D27E	SSTR00001167	815.449,32 €	43.503,53 €	ALAHLISAF
STR-SSTR00001167-2024-Q3	439B06A3-E0EF-4E2C-83E7-A0CED138D27E	SSTR00001167	0, €	-10.240,86 €	ALAHLISAF
STR-SSTR00001250-2024-Q3	32DCA1DD-0758-45A4-86BF-DFB596B9FA1A	SSTR00001250	11.700, €	895,97 €	ALAHLISAF

Dimenstion (Support_Category)

Category	ProfitLowerBoundaries	ProfitUpperBoundaries	RevenueLowerBoundaries	RevenueUpperBoundaries
A	50000	1000000000	250000	1000000000
B	12500	50000	50000	250000

C

-1000000000

12500

-1000000000

50000

Current measure

category_abcProfitEurr =

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

Thanks!

[bhanu_gautam · Accepted solution]

@Okazakipedro , Check this if it meets your requirement

[Okazakipedro]

Hello Bharu, thanks for the reply, but still got the same result...