cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

3 REPLIES 3
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
Anonymous
Not applicable

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

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors