Forum Discussion

jday's avatar
jday
Helper I
8 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
jday
Helper I
8 years ago

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. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    jday,

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




    Regards,
    Lydia

    • jday's avatar
      jday
      Helper I
      8 years ago

      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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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