Forum Discussion

DiDiliz's avatar
DiDiliz
Frequent Visitor
1 year ago
Solved

Separate measure by multiple slicer options

I am trying to create a measure that performs a calculation but adds together the value of the measure when multiple options are selected in the slicer instead of adding everything together.  So for example if person 1 and person 2 are selected in one slicer and October and November are selected in another slicer, I want: 

number of hours for person 1 * average % for person 1 for October

+ number of hours for person 1 * average % for person 1 for November

+ number of hours for person 2 * average % for person 2 for October

+ number of hours for person 2 * average % for person 2 for November,

 

instead of number of hours for October and November and person 1 and person 2 * average % for October and November and person 1 and person 2.  I have already have a measure that will separate out the months selected in a slicer, but when I select multiple people from another slicer it is then totals the number of hours for person 1 & person 2 * average % for person 1 and person 2.  Is there a way to add in the person selection as well as the month selection.  

 

This is the measure I already have for the calculation to add the invidual months together is: 

Measure =
SUMX(
    VALUES('TABLE1'[Week Commencing ].[Month]),
    CALCULATE(
        SUM('TABLE2'[Hours]) * AVERAGE('TABLE1'[Budget %]),
        FILTER(
            'TABLE1',
            'TABLE1'[Week Commencing ].[Month] = EARLIER('TABLE1'[Week Commencing ].[Month])
    )))
 
I need to add the people in it which is TABLE1[Team Member]. 
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    I think you can use

    My Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[WeekCommencing].[Month], Table1[TeamMember] ),
    CALCULATE ( SUM ( 'TABLE2'[Hours] ) * AVERAGE ( 'TABLE1'[Budget %] ) )
)

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    I think you can use

    My Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[WeekCommencing].[Month], Table1[TeamMember] ),
    CALCULATE ( SUM ( 'TABLE2'[Hours] ) * AVERAGE ( 'TABLE1'[Budget %] ) )
)