topic Re: Matrix row and column totals with excluded measure values in DAX Commands and Tips

Matrix row and column totals with excluded measure values

irukandjix — Fri, 01 Dec 2023 14:38:29 GMT

Table without restirictions has two dimensions Color and Number. Value cells has values from a measure (we can call it CellSum) that sums up values with these dimensions. I want to exclude all values below 4 from counted totals, but want to see these values in the matrix. See example. How can we achive this with dax code?

Table without restrictions:

Desired table (red lines just to show which values we want to exclude from totals):

Re: Matrix row and column totals with excluded measure values

OwenAuger — Sun, 03 Dec 2023 04:10:14 GMT

Hi irukandjix

I would suggest a measure along these lines (replace table/column references as needed):

CellSum Modified = VAR Threshold = 4 RETURN IF ( AND ( ISINSCOPE ( Number[Number] ), ISINSCOPE ( Color[Color] ) ), [CellSum], SUMX ( SUMMARIZE ( YourTable, Color[Color], Number[Number] ), VAR CellSum = [CellSum] RETURN IF ( CellSum >= Threshold, CellSum ) ) )

ISINSCOPE is used to detect whether you are in an individual Color/Number cell.

Does something like this work for you?

Regards

Re: Matrix row and column totals with excluded measure values

irukandjix — Sun, 03 Dec 2023 15:56:40 GMT

Very nice solution, thank you OwenAuger ! Your magic does exactly what is wanted!