Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkusEng1998
Resolver II
Resolver II

DAX Calculated column to sum a matrix column

I have a matrix report with [Rooms] in rows, [Program] in the pivot column, and [AreaM] for the value.

I have slicers to change the number of columns displayed [Programs] .

 

I want to filter out rows, where the columns all display zero. So I created a calculated column [AllProgramSum] to "sum up the columns". Yet, this calculation does not seem to correspond to the slicer(s). When I expect this calculated column to be zero, it shows the sum from all the columns...

 

Please advise, how to fix my calculated colunn:

    

AllProgramSum = Calculate(sum('SharepointALL'[AreaM]), ALLEXCEPT('SharepointALL', 'SharepointALL'[Rooms]))
 
Thank you!
MeasureSumofMatrixColumns.jpg

 

In this screen shot, I would like to filter out all of the rows, where the three columns are all red (0.00).

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@MarkusEng1998 Calculated columns are only calculated at data load and thus are not interactive. You would need something like a measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

MarkusEng1998
Resolver II
Resolver II

Thanks, @Greg_Deckler . I am making this more complicated than necessary. This simple measure fixed my issue:

_NoProgAreas = (sum('SharepointALL'[AreaM]))

View solution in original post

2 REPLIES 2
MarkusEng1998
Resolver II
Resolver II

Thanks, @Greg_Deckler . I am making this more complicated than necessary. This simple measure fixed my issue:

_NoProgAreas = (sum('SharepointALL'[AreaM]))
Greg_Deckler
Super User
Super User

@MarkusEng1998 Calculated columns are only calculated at data load and thus are not interactive. You would need something like a measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.