Dynamic measure based on slicer selection

[dokat]

Hi,

I'd like to calculate dynamic measure based on slicer selection. I have 8 values in slicer "Off Invoice Sales,Net Sales, Trade Spend, Volume, Packages, ASP, Gross Profit". Based on slicer selection i would like to execute different measures. For Ex: If Off Invoice Sales is selected i would like to return {Output OIS] measure and so on. I am using measures in stacked column chart 

I tried below dax code however it returns error. See screenshot of the error below. Is there a workaround this issue?

Output Selected Calculation = 
SWITCH(
  SELECTEDVALUE('Output'[P&L]),
   IF(SELECTEDVALUE('Output'[P&L]) = "Off Invoice Sales", CALCULATE([Output OIS])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Net Sales", CALCULATE([Output NS])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Trade Spend", CALCULATE([Output Trade Spend])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Volume", CALCULATE([Output Volume])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Packages", CALCULATE([Output Packages])),
   IF(SELECTEDVALUE('Output'[P&L]) = "ASP",  CALCULATE([Output ASP])),
   IF(SELECTEDVALUE('Output'[P&L]) = "Gross Profit", CALCULATE([Output GP]))
)

 

[Kishore_KVN]

Hello @dokat ,

No need to use IF and CALCULATE inside a SWITCH statement simply you can mention column name and measure names respectively. Your measure should look as below:

Output Selected Calculation = 
SWITCH(
  TRUE(),
   SELECTEDVALUE('Output'[P&L] = "Off Invoice Sales", [Output OIS],
   SELECTEDVALUE('Output'[P&L] = "Net Sales", [Output NS],
   SELECTEDVALUE('Output'[P&L] = "Trade Spend", [Output Trade Spend],
   SELECTEDVALUE('Output'[P&L] = "Volume", [Output Volume],
   SELECTEDVALUE('Output'[P&L] = "Packages", [Output Packages],
   SELECTEDVALUE('Output'[P&L] = "ASP",  [Output ASP],
   SELECTEDVALUE('Output'[P&L] = "Gross Profit", [Output GP],
BLANK()
)

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[dokat]

@Kishore_KVN Wen i tried your formula its giving me following error message 
Too many arguments were passed to the SELECTEDVALUE function. The maximum argument count for the function is 2.

[Kishore_KVN · Accepted solution]

Output Selected Calculation = 
SWITCH(
  TRUE(),
   SELECTEDVALUE('Output'[P&L]) = "Off Invoice Sales", [Output OIS],
   SELECTEDVALUE('Output'[P&L]) = "Net Sales", [Output NS],
   SELECTEDVALUE('Output'[P&L]) = "Trade Spend", [Output Trade Spend],
   SELECTEDVALUE('Output'[P&L]) = "Volume", [Output Volume],
   SELECTEDVALUE('Output'[P&L]) = "Packages", [Output Packages],
   SELECTEDVALUE('Output'[P&L]) = "ASP",  [Output ASP],
   SELECTEDVALUE('Output'[P&L]) = "Gross Profit", [Output GP],
BLANK()
)

Sorry I missed brackets for selectedvalue. Now it will work. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[dokat]

@Kishore_KVN Thank you. It worked for the most part except when i select ASP in slicer it returns blank values. Below is the formula i use for [Output ASP]. Do you know what may cause this issue?

Output ASP = DIVIDE([Output OIS],[Output Volume])

Output OIS = CALCULATE(SUMX(FILTER('Output', 'Output'[P&L] = "Off Invoice Sales" && NOT(ISBLANK('Output'[Values]))), 'Output'[Values]))

Output Volume = CALCULATE(SUMX(FILTER('Output','Output'[P&L] = "Volume"), 'Output'[Values]), NOT(ISBLANK('Output'[Values])))