cancel
Showing results for
Did you mean:
Resolver I

## How to calculate sum of measures based on multiple slicer selection

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.
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])="Subk",[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.

1 ACCEPTED SOLUTION
Resolver I

@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.

2 REPLIES 2
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Resolver I

@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.

Announcements

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

#### Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors