cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors