Forum Discussion
Use measure as chart legend
Hello Power BI super heroes!
I'd like to use 'Retail Excess' measure as a legend for a chart, but having trouble with it. May I know how I can make it?
I tried to find solutions from articles in ommunity, but couldn't.
Measures
First, measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context. So you could not put it into Legend of a visual.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your case, you could this way as below:
Step1:
You need a table that contains all the result of this measure, for example:
Step2:
Create a measure like this logic:
Measure 3 = var _table=FILTER(CROSSJOIN(ADDCOLUMNS('Fact',"_type",[Measure]),'Type'),[_type]=[Type]) return COUNTROWS(_table)You could also use SUMX/MAXX/MINX instead of COUNTROWS in the formula
Result:
here is sample pbix file, please try it.
Regards,
Lin
4 Replies
- Pragati11
Super User
HI Junminkim0214 ,
You can never use a MEASURE as a legend in the charts. They have to be always COLUMNS.
Convert your measure as a calculated column, then use it as a legend in your chart.
Thanks,
Pragati
Hi Pragati11
Thanks for your advice!
I created a column like below, but the results are not correct. May I know a reason for this?
Column
Retail Excess column = if([Retail WOC]>=12,"Excess","Non-Excess")
Measure
Retail Excess = if([Retail WOC]>=12,"Excess","Non-Excess")
Best regards,
Jun
v-lili6-msftCommunity Support
First, measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context. So you could not put it into Legend of a visual.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your case, you could this way as below:
Step1:
You need a table that contains all the result of this measure, for example:
Step2:
Create a measure like this logic:
Measure 3 = var _table=FILTER(CROSSJOIN(ADDCOLUMNS('Fact',"_type",[Measure]),'Type'),[_type]=[Type]) return COUNTROWS(_table)You could also use SUMX/MAXX/MINX instead of COUNTROWS in the formula
Result:
here is sample pbix file, please try it.
Regards,
Lin
