Forum Discussion
Measure with multiple format types
I am trying to create a measure in power bi that has the possibility of displaying as a percent or a rate per 1000. I need to be able to display these numbers in a bar chart.
I have found one possible solution to this problem, however it turns the values within the measure into a text. This prevents me from creating a bar chart. To get this result I am using the following dax equation.
Measure = IF(HASONEVALUE(Column),
SWITCH(VALUES(Column),
"Value",FORMAT([Measure]*12000,"Standard"),
"Value",FORMAT([Measure]*12000,"Standard"),
FORMAT([Measure],"Percent")))
13 Replies
To the best of my knowledge you can only return a single data type from a measure and a measure can only be formatted in one way unless you turn it into text.
Is there any way to have two measures applied within a bar chart and have a slicer be the decider on which results to choose? I am looking for any method that could be used to generate the desired results.
If the measures return different value types, and you create a single measure to decide between them, you are probably going to run afoul of the issue with a single measure returning two different data types.
If you can clarify what you are going after I think that would help. Is this a correct problem statement:
You have two measures. One is displayed as a decimal, the other is displayed as a %. So, the first measure you want something like 0.45 displayed and for the second 45%. You want to have both of these measures in a bar chart and have some way to only display one or the other.
Is that a correct problem statement?
jday,
FORMAT() function will change the data type of your measure to Text, and you are not able to drag the measure to axis of bar chart.
In your scenario, create two measures, use the Format option under Modeling ribbon to change format for the two measures, then follow the guide in the blog below to display the measures with different format based on slicer selection.
http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html
Regards,
LydiaThis might be the closest I can get to solving this problem. If I do this my numbers display correctly, but the formatting is not being applied. My percents display as a decimal (.35). I would like for the percentages to actually show up as a percent (35%). Could you provide me an example where the formatting works? In the example your provided they are only using whole numbers.
I am aware I can just multiply by 100 to get a percentage rate, but I need the percentage sign to be there. This will provide the least amount of confusion possible to my audience.
Thank you for getting me this close.
jday,
Click on your measure in the Fields panel, then select Percentage under Modeling->Format.
Regards,
Lydia
