topic Re: Add multiple selected project values in DAX Commands and Tips

Add multiple selected project values

gprouse — Tue, 07 May 2024 07:57:52 GMT

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?

Re: Add multiple selected project values

HikingBIKing — Tue, 07 May 2024 08:44:43 GMT

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

Re: Add multiple selected project values

gprouse — Tue, 07 May 2024 08:55:47 GMT

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