Measure with multiple format types

[jday]

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")))

[lg1551]

This should help:

https://powerbi.microsoft.com/en-ie/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

[v-yuezhe-msft]

@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,
Lydia

[jday]

This 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. 

[v-yuezhe-msft]

@jday,

Click on your measure in the Fields panel, then select Percentage under Modeling->Format.




Regards,
Lydia

[jday]

Sorry I was unclear.  When I do this the percentage format is not being applied to the switch statement logic.  Below you can find an example of what my data looks like and what happens step by step.  

MeasureCode SubMeasureCode Numerator Denominator
M01                SM001                   1,000         5,000
M02                SM002                   1,001         5,005
M01                SM003                   1,002         5,010
M02                SM004                   1,003         5,015
M03                SM005                   1,004         5,020
M03                SM006                   1,005         5,025
M03                SM007                   1,006         5,030
M04                SM008                   1,007         5,035
M04                SM009                   1,008         5,040
M04                SM010                   1,009         5,045
M06                SM011                   1,010         5,050
M06                SM012                   1,011         5,055
M07                SM013                   1,012         5,060
M07                SM014                   1,013         5,065
M01                SM015                   1,014         5,070
M01                SM016                   1,015         5,075
M08                SM017                   1,016         5,080
M09                SM018                   1,017         5,085
M10                SM019                   1,018         5,090
M09                SM020                   1,019         5,095
M10                SM021                   1,020         5,100
M06                SM022                   1,021         5,105
M06                SM023                   1,022         5,110
M11                SM024                   1,023         5,115
M11                SM025                   1,024         5,120
M10                SM026                   1,025         5,125
M03                SM027                   1,026         5,130
M03                SM028                   1,027         5,135
M04                SM029                   1,028         5,140

Power Bi Formulas 

Percent = SUM('Raw Numbers'[Numerator ])/SUM('Raw Numbers'[Denominator])  ---- Formatted as a percentage 

Whole Number = SUM('Raw Numbers'[Numerator ])/SUM('Raw Numbers'[Denominator])*12000 ----- Formatted as a whole number 

New table created (RefTable) using the measure/submeasure columns above.  There is no relationship connecting my raw number table and the new table. 

SwitchLogic = SWITCH( FIRSTNONBLANK(RefTable[SubMeasureCode],RefTable[SubMeasureCode])
,"SM020",[Whole Number]
,"SM021",[Whole Number]
,[Percent])

Once this step is applied my percents will display as .35 instead of (35%). 

Is there something I am missing? 

[v-yuezhe-msft]

@jday,

You need to create a table(assume it called RefTable) containing a column named measure which has these values(Percent, Whole Number), then create the following measure 

SwitchLogic = SWITCH( FIRSTNONBLANK(RefTable[Measure],RefTable[Measure])
,"Percent",[Percent]
,"Whole Number",[Whole Number])

More details are descibed in the following blog.
http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html

Regards,
Lydia

[jkuttler]

It doesn't appear that this works. The chart will not respect the format (designated from the ribbon, not from a format statement) of each individual measure within the switch statement. This would be a great feature if it did work because it would allow for a lot more dynamic chart behavior.

[Greg_Deckler]

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.

[jday]

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. 

[Greg_Deckler]

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]

This is kind of what I am looking for.  I have a group of metrics that I need to report.  There is a slicer so you can determine which metric you are viewing.  It is possible for metric A to be represented with a percentage (formated as a percentage) and metric B to be represented as a whole number. 

The percentage is created using Numerator/Denominator 
The whole number is created using Numerator/Denominator*12,000

[Greg_Deckler]

You *might* be able to get there by wrapping the measure in an IF statement and using FORMAT...

[lg1551]

This is the problem I am currently having. It works in a table but doesn't seem to work in a bar chart for some reason.

I have two measures. One should always be a decimal, the other is always a decimal UNLESS a slicer selection is made, to which it should become a percent. I have the different sub-measures written for the logic to work. If I use the toggle measure in a table visual it works fine but not in a bar chart. I'm assuming because FORMAT converts it to TEXT so it cannot plot along the axis. Now I'm wondering if a Calculation Group might solve the problem by conditionally outputting the format based on slicer selection, yet still plot the axis based on the original decimal value. Just thinking out loud here.