Classification of customer issue

[brentb]

Hi,

I am working on grouping our customers into new, active, lost & oneoff. To do so I made 3 measures in my customer table:

1. CustomerTurnover

2. CustomerTurnoverN-1

3. CustomerturnoverN-2

I put these in a switch formula to make a new measure 'SalesClass':

SWITCH (

TRUE (),

[CustomerTurnover] > 0 && [CustomerTurnoverN-1] > 0, "ACTIVE",

[CustomerTurnoverN-1] > 0 && [CustomerTurnoverN-2] > 0 && [CustomerTurnover] <= 0, "LOST",

[CustomerTurnoverN-1] <= 0 && [CustomerTurnover] > 0, "NEW", [CustomerTurnover] <= 0 && [CustomerTurnoverN-1] > 0 && [CustomerTurnoverN-2] <= 0, "ONEOFF",

"N/A"

)

This all works fine and when I put these in a simple powerBI table I get the result I want. However, when I want to put these in a stacked column chart, I run into a problem. I basically want to see the number of customers per SalesClass, but I cannot use the 'SalesClass' measure as a field for the legend of the stacked column chart, because it's a measure. 

One solution I came up with didn't work. Instead of making everything as a measure, I tried to put all the formulas in as calculated columns, but that doesn't work. When I try to rebuild those 3 measures mentioned above into calculated columns, I get empty columns for N-1 and N-2. I tried these:

CALCULATE (

SUMX ( RELATEDTABLE ( 'factSalesValueEntry' ), [SalesAmount_RCY] ),

FILTER ( ALL ( 'Date' ), 'Date'[Date] IN DATEADD ( 'Date'[Date], -1, YEAR ) ) )

CALCULATE ( SUMX ( RELATEDTABLE ( 'factSalesValueEntry' ), [SalesAmount_RCY] ), DATEADD ( 'Date'[Date], -1, YEAR ) )

SUMX ( RELATEDTABLE ( 'factSalesValueEntry' ), [SalesAmountN-1 )

Hopefully, anyone knows a fix or a workaround for my goal.

[johnt75]

Have a read of https://www.daxpatterns.com/dynamic-segmentation/, I think that's what you're looking for.

[brentb]

Thanks, I do think this is the right direction. However, I get stuck because the way I group my dealers is a bit different than the author's. I want to group my dealers with a SWITCH formula, but when I process that into the formula of the author it doesn't work.