DAX function to return numeric or percent values depending on metric used

[Anonymous]

Hi All,

I need to visualize certain metrics on a single line chart and the y axis needs to switch between numeric and percent based values on a selected metric.

The DAX I've written is shown below. When selecting different metrics, the values only show numeric whole numbers for values where I need it to be a percentage. Any help would be much appreciated, personally I think I am using the wrong DAX function after the RETURN bit but not sure what DAX function to use. 

DAX written:

Retention Trend Charts Measure =

VAR _selectedTrend = [_Selected Trend ID (Retention)]

VAR _data =

SWITCH(TRUE(),

-- Fixed Term

_selectedTrend =1,[Customers Up for Renewal],

_selectedTrend =2,[Customers Retained],

_selectedTrend =3,[Customer Retention Rate]*100,

_selectedTrend =4,ROUND([SqM Up for Renewal],0),

_selectedTrend =5,ROUND([SqM Retained],0),

_selectedTrend =6,[Customer Retention Rate]*100,

_selectedTrend =7,CALCULATE([Monthly Revenue Lost (Renewal Terminations)], 'product'[Product Group] = "Office"), --office

_selectedTrend =8,CALCULATE([Monthly Revenue Lost (Renewal Terminations)], 'product'[Product Group] = "Virtual Office"), -- v.office

_selectedTrend =9,[Monthly Revenue Lost % (Renewal Terminations)],

_selectedTrend =10,[Deals Discounted % (Renewal)]*100,

_selectedTrend =11,[Discount Depth (Renewal)],

_selectedTrend =12,[Price Increase Achived (L3M) % (Renewal)]*100,

-- office

_selectedTrend =13,ROUND([MtM Active Contracts],0),

_selectedTrend =14,ROUND([MtM Terminated Contracts],0),

_selectedTrend =15,[Churn Rate % MTM (Renewals)]*100 ,

_selectedTrend =16,ROUND([Active MtM Contracts Average Tenure],1),

_selectedTrend =17,ROUND([Terminated MtM Contracts Average Tenure],1),

-- v.office

_selectedTrend =18,ROUND([MtM Active Contracts],0),

_selectedTrend =19,ROUND([MtM Terminated Contracts],0),

_selectedTrend =20,[Churn Rate % MTM (Renewals)]*100,

_selectedTrend =21,ROUND([Active MtM Contracts Average Tenure],1),

_selectedTrend =22,ROUND([Terminated MtM Contracts Average Tenure],1)

)

RETURN

SWITCH( TRUE(),

_selectedTrend =1,ROUND((_data),0),

_selectedTrend =2,ROUND((_data),0),

_selectedTrend =3,ROUND((_data * 100),1),

_selectedTrend =4,ROUND((_data),0),

_selectedTrend =5,ROUND((_data),0),

_selectedTrend =6,ROUND((_data * 100),1),

_selectedTrend =7,ROUND(_data,0),

_selectedTrend =8,ROUND(_data,0),

_selectedTrend =9,ROUND((_data * 100),1),

_selectedTrend =10,ROUND((_data * 100),1),

_selectedTrend =11,ROUND(_data,0),

_selectedTrend =12,ROUND((_data * 100),1),

_selectedTrend =13,ROUND(_data,0),

_selectedTrend =14,ROUND(_data,0),

_selectedTrend =15,ROUND((_data * 100),1),

_selectedTrend =16,ROUND(_data,0),

_selectedTrend =17,ROUND(_data,0),

_selectedTrend =18,ROUND(_data,0),

_selectedTrend =19,ROUND(_data,0),

_selectedTrend =20,ROUND((_data * 100),1),

_selectedTrend =21,ROUND(_data,0),

_selectedTrend =22,ROUND(_data,0),

_data)

Photo below should be showing percentage values;

Your help would be most appreciated on this matter!

[Anonymous]

If you want to dynamically format measures, you have to use calculation groups.

[Anonymous]

@Anonymous Thank you for this, I am trying to use calculation groups in Tabular editor however, the following measures I am trying to input into the calculation group do not work when used in the line chart, what is wrong with the following x5 DAX expressions?