Forum Discussion
How to create a single measure that dynamically switches between showing a number or a percentage..
- 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 _2For fomatting use same code.
IF( SELECTEDVALUE( Sctsht[Tag] )= "MomValue", "#,##0", IF( SELECTEDVALUE( Sctsht[Tag] ) ="MomPct", "0%","#,##0" ) )Below screenshot
Attached is the pbix file
Hope, it will help.
Regards
sanalytics
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
Hope, it will help.
Regards
sanalytics