Forum Discussion

mogugu_84's avatar
mogugu_84
Icon for Helper I rankHelper I
1 year ago
Solved

Switch measure using parameter in SUM formulas

Hi    i have a created a parameter (called "choose measure" to switch between unit and cash) and it works for simple charts with no calculated field   the parameter looks like this:   Choose ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi mogugu_84 ,

     

    As far as I know, if you want to use Field parameter function, you need to add all measures you need in it instead of only add the column.

    For example, you need create two measures [LYTD Retail Cash] and [LYTD Retail Units] for LYTD Retails, and the same for others like YTD Retail and FYTD Growth Retail.

    I suggest you to add a calculated column in this parameter table.

    Choose measure = IF(CONTAINSSTRING(Parameter[Parameter],"Cash"),"Retail Cash","Retail Units")

    Then use this column to create a slicer. Result is as below.

     

     

    Best Regards,
    Rico Zhou

     

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

     

     

     

mogugu_84's avatar
mogugu_84
Icon for Helper I rankHelper I
1 year ago
 

 Hi, thank you, i tried to follow the example in your first link,

 

below is the example fields: 

 

Revenue =
    SWITCH(
        SELECTEDVALUE(Revenue[Revenue Fields]),
        "'Key mesaures'[Revenue with taxes]", [Revenue with taxes],
        "'Key measures'[Revenue without taxes]", [Revenue without taxes]
    ) 

 

example parameter table looks like this:

RevenueRevenue FieldsRevenue Order
Revenue with taxes
'Key measures'[Revenue with taxes]0
Revenue without taxes'Key measures'[Revenue without taxes]1

 

here's my version, 

 

Retail =
    SWITCH(
        SELECTEDVALUE('Choose measure'[Choose measure Fields]),
        "'Def market'[Retail Value]", [Retail Value],
        "'Def market'[Retail Volume]", [Retail Volume],
    )

 

my table of parameter

 

but the results says "argument "6" is required....

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
1 year ago

hi mogugu_84 ,

 

try to leave out the last comma.

  • mogugu_84's avatar
    mogugu_84
    Icon for Helper I rankHelper I
    1 year ago

    Thanks again Freeman, yes, that makes the formula tick, but sorry, the whole method still just doesn't work...

     

    here's one of the formula for growth calculation 

     

    LYTD Retail =
    CALCULATE([Retail Volume],DATESYTD(dateadd('Calendar'[Date],-1,Year),"3/31"))
     
    Here's the switch formula
    Retail =
        SWITCH(
            SELECTEDVALUE('Test Parameter'[Test Parameter Fields]),
            "'Def market'[Retail Value]", [Retail Value],
            "'Def market'[Retail Volume]", [Retail Volume]
        )
    Parameter table:
     
     
     
    but finally, the numbers don't change when i use this parameter......not sure where it went wrong