topic Need to aggregate multiple values into single value/ignore the filter context (DAX) in DAX Commands and Tips

Need to aggregate multiple values into single value/ignore the filter context (DAX)

Anonymous — Thu, 26 Nov 2020 15:41:44 GMT

I have a formula as follows that provides the correct data but not as I want it displayed (eg when in Excel pivot table)

What I want it to do is to provide the same total value regardless of whether I add the "mtrc_nm" into the rows area of the pivot table

Here is the formula:

mtrc_all_tcob_ttl:= CALCULATE ([mtrc_ttl], AND(TCOB[mtrc_nm] <> "rma_crdt" , TCOB[mtrc_nm] <> "net_rev")

Here is how it looks in the pivot table:

However, the following image is how I want it to appear -ie; I want it to ignore the "mtrc_nm" dimension (I think this is called ignoring the filter context??)

I think I need to use the "ALL" or "ALLEXCEPT" function somehow but can't figure it out.

Appreciate any advice!!

thanks

Fergal

Re: Need to aggregate multiple values into single value/ignore the filter context (DAX)

MattAllington — Thu, 26 Nov 2020 19:32:20 GMT

mtrc_all_tcob_ttl:= CALCULATE ([mtrc_ttl], ALL(TCOB[mtrc_nm])

Re: Need to aggregate multiple values into single value/ignore the filter context (DAX)

Anonymous — Thu, 26 Nov 2020 19:48:01 GMT

hi Matt

thanks for the info but that would not exclude the metrics I need excluded

[mtrc_nm] <> "rma_crdt" and [mtrc_nm] <> "net_rev"