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

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