Measure for Average of Total Areas is not filtering by the slicer

[MarkusEng1998]

I have a matrix with projects in the columns, and rooms in the rows. The value is the program area. I have slicers for the region, program, etc. which filter out the projects and/or rooms.

I created a DAX measure _ProgAreaSum to calculate the average total program areas for the selected projects.

_ProgAreaSum = Calculate(Sum(_projRmTemplatesNormalized[rmProgArea]), ALLEXCEPT(_projRmTemplatesNormalized, _projRmTemplatesNormalized[Project]))/[_ProjCnt]

The dashboard shows that I used the Region slicer to display two projects. The expected value for _ProgAreaSum should be 916,542.5, not 33.35 million. The measure is taking the sum from all of the projects, not the two. And then is dividing by the _ProjCnt measure (2).

Please advise. I am confused about the proper DAX expression.

Thank you!

[MarkusEng1998 · Accepted solution]

I made this too difficult. I do not need the calculate in the function.

_ProgAreaSum = Sum(_projRmTemplatesNormalized[rmProgArea])/[_ProjCnt]

Solution provided by my colleague Robert Manna.

[v-rongtiep-msft]

Hi @MarkusEng1998 ,

If I have not understood your meaning, there is some error on the measure total ?

I have found a sumilar post, please refer to it to see if it helps you.

Solved: How to remove duplicates count - Microsoft Power BI Community

_contract_ = var _a = '_projRmTemplatesNormalized'[_progareasum]
var _b = SUMMARIZE('_projRmTemplatesNormalized','_projRmTemplatesNormalized'[Program],"aaa",'_projRmTemplatesNormalized'[_progareasum])
return
IF(HASONEVALUE('_projRmTemplatesNormalized'[Program]),_a,averagex(_b,[aaa]))

If it still does not help, please provide your pbix file without privacy inforamtion.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarkusEng1998]

Hi @v-rongtiep-msft , I wanted the average totals to be limited by the slicer. There are only two projects in the screenshot, but the average includes all of the projects.

btw. How do I attach a PBIX file?