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!!

     

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