cancel
Showing results for 
Search instead for 
Did you mean: 
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

2 REPLIES 2
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

@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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors