Forum Discussion

DiDiliz's avatar
DiDiliz
Frequent Visitor
1 year ago
Solved

Add multiple selectedvalues

I am trying to create a measure which will add up the separate values which are selected in the slicer.  However, because each value has a different calculation, I don't want it to add up the whole selection, I want it to add A to B to C etc.  

 

I have a graph showing hours per month, I also have a another graph showing the same data but totalled for the months selected.  The hours I need are calculated using an average figure for each month, but if I select all months it averages the figures from all months rather than using the average for May + the average for June etc. 

 

I tried using 

Test Measure 2 = CALCULATE(IF(SELECTEDVALUE('TABLE1'[Week Commencing ].[Month]) = "May", SUM('TABLE2'[Hours]) * AVERAGE('TABLE1'[Budget %]),0.0)) + CALCULATE(IF(SELECTEDVALUE('TABLE1'[Week Commencing ].[Month]) = "June", SUM('TABLE2'[Hours]) * AVERAGE('TABLE1'[Budget %]),0.0))

 

however this only returns a value when I select one month at a time, it does not add the sum of May to the sum of June. 

 

Is there a way to add selected values to each other rather than lumping them together into a total sum. 

 

Thanks

Need Help
  • Angith_Nair's avatar
    Angith_Nair
    1 year ago

    Hi DiDiliz 

    Use the below dax:

    Test 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])
        )
    )
)

5 Replies

Replies have been turned off for this discussion
  • anmolmalviya05's avatar
    anmolmalviya05
    Super User
    1 year ago

    Hi DiDiliz , Please try the below measure

    Test Measure 2 =
    SUMX(
    VALUES('TABLE1'[Week Commencing].[Month]), -- Iterate over selected months
    SWITCH(
    TRUE(),
    'TABLE1'[Week Commencing].[Month] = "May", SUM('TABLE2'[Hours]) * AVERAGE('TABLE1'[Budget %]),
    'TABLE1'[Week Commencing].[Month] = "June", SUM('TABLE2'[Hours]) * AVERAGE('TABLE1'[Budget %]),
    0 -- Default value for other months or no selection
    )
    )

    • DiDiliz's avatar
      DiDiliz
      Frequent Visitor
      1 year ago

      It's letting me select more than one month, however it's doubling the total.  So May should be 74 but it's showing 149, June should be 67 but together with May it's showing 284. 

       

      This does feel like progress though.  Thank you 

      • Angith_Nair's avatar
        Angith_Nair
        Continued Contributor
        1 year ago

        Hi DiDiliz 

        Use the below dax:

        Test 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])
        )
    )
)
  • DiDiliz's avatar
    DiDiliz
    Frequent Visitor
    1 year ago

    I have now realised that I am having the same problem when filtering people with the slicers.  Rather than adding person 1 + person 2 + person 3, it's averaging the budget % over all 3 people to give the result.  Is there a way to add a second filter to the above measure to separate out people as well.  The data for people is in the same table as the months above.