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

[BarbaraYanChen]

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. 

Data see below screenshot: I have two columns in the dataset, one is "Value Only" with number type, and the other one is "Pct Only" with percentage type.

Goal see below screenshot: I want to create a measure that can swith between number and percentage types

FYI:  it can't be text type as the value will be used for calculation (sum) in aggreated level as a total.

 

Thanks,

Barbara

[johnt75]

Create separate measures, one to show the value and one to show the %, and set the appropriate format strings. Create a field parameter, add those 2 measures to it and add a slicer to the page. Use the field parameter in your visual instead of the individual measures and the user will be able to switch which is displayed by using the slicer.

[sanalytics · Accepted solution]

@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

[Poojara_D12]

Hi @BarbaraYanChen 

Create a Slicer Table: Add a new table with options like "Show Value" and "Show Percentage":

SwitchTable = DATATABLE("Selection", STRING, {{"Show Value"}, {"Show Percentage"}})

Create a Dynamic Measure:

DynamicMeasure = 
SWITCH(
    SELECTEDVALUE(SwitchTable[Selection], "Show Value"),
    "Show Value", SUM('YourTable'[Value Only]),
    "Show Percentage", SUM('YourTable'[Pct Only])
)

Add a Slicer: Use SwitchTable[Selection] as a slicer on the report.

Use the Measure: Add DynamicMeasure to your line chart.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[lbendlin]

You are trying to do too much.  Use separate columns.