topic Re: I can’t use measure as a chart legend to categorize the data in DAX Commands and Tips

I can’t use measure as a chart legend to categorize the data

HeevaCh — Sun, 19 May 2024 07:42:30 GMT

Hello!

I have a dataset like this:

Date	Client	Total Sales	Sales Prev	Diff Sales	MRR Measure
2024/January	A	200		200	New
2024/February	A		500	-200	Lost
2023/January	B	100			New
2024/January	B	650		150	Reactivation
2024/February	B		150	-150	Lost
2023/December	E	11885			New
2024/January	E	6265	11885	-10670	Reduction
2024/February	E		1515	-1215	Lost
2024/March	E	6270		6970	Reactivation
2024/April	E	64850	6990	7880	Expansion
2024/May	E	3650	14850	-11700	Reduction

I have written a DAX code as a measure. The code is as follows:

MRR Measure = if([firstD]=1,"New", if(and([Total Sales]>=0,[Sales Prev]=0),"Reactivation", if([Diff Sales]>=0,"Expansion", if(and([Total Sales]=0,[Sales Prev]<>0),"Lost", if([Diff Sales]<0 , "Reduction")))))

and the code for the Different Sales:

Diff Sales = [total sales]-[sales prev]

I want to add a bar chart based on this different sales value and the date. It gives me something like this:

I want something like below, but because I have written a measure I can’t move it to the legend field in the chart. What should I do?

Re: I can’t use measure as a chart legend to categorize the data

xifeng_L — Sun, 19 May 2024 10:17:59 GMT

Hi HeevaCh ,

You can try below solution.

#1. Create an auxiliary table to use as a legend field. Such as:

//New Table Expression

LegendTable = 
DATATABLE(
    "Item",STRING,
    "Index",INTEGER,
    {
        {"Measure1",1},
        {"Measure2",2},
        {"Measure3",3}
    }
)

#2. Create a dynamic measure that can calculate different measures based on different legend items.

//Measure Expression

AutoIndicator = 
VAR CurIndicator = SELECTEDVALUE('LegendTable'[Item])
RETURN
SWITCH(
    CurIndicator,
    "Measure1",[Measure1],
    "Measure2",[Measure2],
    "Measure3",[Measure3]
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: I can’t use measure as a chart legend to categorize the data

HeevaCh — Sun, 19 May 2024 13:24:01 GMT

I'm sorry but It didn't work.
It's not what I want. I want the values in the chart to be catogorized. For example if the january had :
100 Expansion
100 New
-200 reduction
-50 Lost
It would be a bar with -50 value. and the bar would show that it was 100 new, -200 reduction and 100 expansion that resulted in -50.

Not necessarily a bar chart. Any chart that you recommend that would be appropriate.

I hope I have explained it well 🙂