Forum Discussion

gprouse's avatar
gprouse
Frequent Visitor
2 years ago
Solved

Add multiple selected project values

My visual is summarizing project values incorrectly since it is adding up the number of months of each project in the denominator. 

Here we can see that the values are being summarized incorrectly in the total value:

The formula I use for this column is: 

Sum Baseestimat DG0 split per month rolling = IF( [Sum Baseestimat DG0 split per month] = 0,
                                                BLANK(),
    CALCULATE([Sum Baseestimat DG0 rolling]/[Sum of Months in DG0],USERELATIONSHIP('model PPM SAP Project Decision Date Final'[Shifted Project Period Dates],'Calendar'[Date]),'model PPM SAP Project Decision Date Final'[Shifted DG0 Period] = 1))
 
I was thinking of solving this problem by making a new measure with variables where I add the normal measure to a separate SELECTEDVALUE() measure, however this won't work since I see that SELECTEDVALUE() does not work when multiple slicer filters are selected.
 
Any ideas how I could summarize just the values selected seperatley for this measure so that the total value is 3,600,000?

 

  • HikingBIKing's avatar
    HikingBIKing
    2 years ago

    Hi gprouse 

    You'll need to use the SUMX function to achieve the total value as you describe it.

    I'd need a bit more information about your model, tables, and the calculations involved in the existing measures to be able to advise beyond this, but a SUMX would perform a calculation with the row context of a table, and then sum up the results, which is what you are trying to achieve.

    SUMX function (DAX) - DAX | Microsoft Learn

2 Replies

  • HikingBIKing's avatar
    HikingBIKing
    Regular Visitor
    2 years ago

    Hi gprouse 

    You'll need to use the SUMX function to achieve the total value as you describe it.

    I'd need a bit more information about your model, tables, and the calculations involved in the existing measures to be able to advise beyond this, but a SUMX would perform a calculation with the row context of a table, and then sum up the results, which is what you are trying to achieve.

    SUMX function (DAX) - DAX | Microsoft Learn

    • gprouse's avatar
      gprouse
      Frequent Visitor
      2 years ago
      Thank you for your help! I managed to fix it by adding sumx to my formula. 
       
      Sum Baseestimat DG0 split per month rolling = IF( [Sum Baseestimat DG0 split per month] = 0,
                                                      BLANK(),
          CALCULATE(SUMX('model PPM SAP Project Cost',[Sum Baseestimat DG0 rolling]/[Sum of Months in DG0]),USERELATIONSHIP('model PPM SAP Project Decision Date Final'[Shifted Project Period Dates],'Calendar'[Date]),'model PPM SAP Project Decision Date Final'[Shifted DG0 Period] = 1))