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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.