Forum Discussion

BarbaraYanChen's avatar
1 year ago
Solved

How to create a single measure that dynamically switches between showing a number or a percentage..

Hello,   I am making a line chart to show MoM trending in Power BI.  I want to create a single measure that dynamically switches between showing a number or a percentage on the slicer selection.  ...
  • sanalytics's avatar
    1 year ago

    BarbaraYanChen 
    Easiest way to create a separate disconneted table for slicer and create a combined measure for this.

     

    Combined Measure = 
    IF(
        SELECTEDVALUE( SlicerTable[Sort] )= 1,
        [MomValue],
    IF(
        SELECTEDVALUE( SlicerTable[Sort] ) = 2,
        [MomPct],[MomValue]
    ) )

     

    For Percntage formatting use measure's Dynamic format option and use below measure

     

    IF(
        SELECTEDVALUE( SlicerTable[Sort] )= 1,
        "#,##0",
    IF(
        SELECTEDVALUE( SlicerTable[Sort] ) =2,
        "0%","#,##0"
    ) )

     

    Below screenshot

    But, if you want to dare to see your screenshot result then create calculated table and use below code ( ideal way to create this table in Power query by simply unpivoting).

     

    Sctsht = 
    VAR _1 = 
      SUMMARIZE( 'Table','Table'[Month] )
     VAR _2 = 
     UNION(
      ADDCOLUMNS(
    	_1,"Value and Pct",[MomValue],
    	"Tag","MomValue","Sort",1,"MonthSort",IF( 'Table'[Month] = "JAN",1, IF( 'Table'[Month] = "FEB",2 ) )
      ),
      ADDCOLUMNS(
    	_1,"Value and Pct",[MomPct],"Tag","MomPct","Sort",2,"MonthSort",IF( 'Table'[Month] = "JAN",1, IF( 'Table'[Month] = "FEB",2 ) )
      ) )
      
    RETURN
    _2

     

    For fomatting use same code.

     

    IF(
        SELECTEDVALUE( Sctsht[Tag] )= "MomValue",
        "#,##0",
    IF(
        SELECTEDVALUE( Sctsht[Tag] ) ="MomPct",
        "0%","#,##0"
    ) )

     

     

    Below screenshot

    Attached is the pbix file

    https://we.tl/t-pCBQoFQDhz

     

    Hope, it will help.

     

    Regards

    sanalytics