How to calculate sum of measures based on multiple slicer selection

[sujitjena]

Hi,

I have a data model with values in four columns as given below - Business Development (BD), Total Payroll (Payroll), Subk, Load.

I have created a disconnected table to create a slicer as given below:

The below measure works for single selection, however, it doesnt work for multiple selections. 

IF(SELECTEDVALUE('Payroll Selection'[Selection])="Payroll",'Table'[payroll],IF(SELECTEDVALUE('Payroll Selection'[Selection])="BD",'Table'[BD],IF(SELECTEDVALUE('Payroll Selection'[Selection])="Subk",'Table'[Subk],IF(SELECTEDVALUE('Payroll Selection'[Selection])="Load",'Table'[Load]))))

Also tried below measure but didnt work,

YoY_Total_Payroll = SWITCH(

TRUE(),

MAX('Payroll Selection'[Selection])="Payroll",[ payroll],

MAX('Payroll Selection'[Selection])="BD",[BD],

MAX('Payroll Selection'[Selection])="Load",[Load],

MAX('Payroll Selection'[Selection])="Subk",[Subk],

CALCULATE([ payroll]+[BD]+[Load]+[subk]))

What i want is - if the slicer is selected for multiple options (Payroll & BD), the result should sum up the values of individual measures. Any suggesion would be helpful.

[TomMartens]

Hey @sujitjena ,

I would use a slightly different approach ...
You can create an unrelated table that holds all the options, but also uses a binary value (2^x) that encodes the options, the following table shows an example:

Then create a measure like the one below:

sum binarycode = SUM( 'unrelated table'[binarycode] ) 

Due to the nature of the binary values, you know what has been selected in the slicer.

Then create a measure like the one below (please be aware that it is not complete 🙂 )

final measure = 
var SelectedOptions = [sum binarycode]
return
SWITCH(
    SelectedOptions
    , 1 , [Measure A]
    , 2 , [Measure B]
    , 3 , [Measure A] + [Measure B]
    , 4 , [Measure C]
    , 5 , [Measure A] + [Measure C]
    , 6 , [Measure B] + [Measure C]
    , 7 , [Measure A] + [Measure B] + [Measure C]
    , 8 , [Measure D]
    // all the other combinations
) 

Creating the measure above is simple but can become a little tedious, depending on the number of options.

Hopefully, this provides an idea of how to tackle this.

Regards,

Tom

[Anonymous]

It's workinggggggggggg!!! Tks a lot Tom.

[sujitjena · Accepted solution]

@TomMartens Thanks for the approach, but as you rightly said its tedious to implement. Instead i decided to change the data structure to unpivot the four columns and that way it will automatically add up when selected.