Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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!

 

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

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    4 years ago

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

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

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