Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sujitjena
Resolver I
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.
Table.PNG
 
I have created a disconnected table to create a slicer as given below:
Slicer.PNG
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.
 
1 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.

View solution in original post

3 REPLIES 3
TomMartens
Super User
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:
image.png
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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors