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.
