Forum Discussion

dokat's avatar
dokat
Post Prodigy
3 years ago
Solved

Dynamic measure based on slicer selection

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 sel...
Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    3 years ago
    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's avatar
dokat
Post Prodigy
3 years ago

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's avatar
Kishore_KVN
Solution Sage
3 years ago
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's avatar
    dokat
    Post Prodigy
    3 years ago

    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])))