Exclude Sales Amount for specified matrix row only from the totals rows

[WinterMist]

Hello - 

Currently, I have a matrix visual as follows:

Matrix Rows:

- 'D Region'[Name]

- 'D SalesRep'[Type]

- 'D SalesRep'[Name]

- 'D Stage'[Name]

Matrix Values (are all measures in the following form, differing only by Month #):

Month 1 =

CALCULATE(

    [Sales Amount],

    'Date'[Month Number] = 1

)

The goal is to EXCLUDE [Sales Amount] of "Written" from all the parent total matrix rows without removing it from the "Written" matrix rows.

Desired results are shown below.

Notice how each of the highlighted yellow cell value amounts for "Written" is EXCLUDED from all of its parent total matrix rows.

Month 1 = 37+ 45 + 10 = 92 (But I need to show 82 for all totals, excluding the 10 for Written)

Month 2 = 9 + BLANK + BLANK = 9 (But I need to show 0 for all totals, excluding the 9 for Written)

Month 3 = [No need to do anything special because there is no "Written" amount]

Month 4 = [No need to...]

Month 5 = 44 + BLANK + 16 = 60 (But I need to show 44 for all totals, excluding the 16 for Written)

Regards,

Nathan

[OwenAuger · Accepted solution]

Hello Nathan,

To exclude 'D Stage'[Name] = "Written" for any totals at a higher level than 'D Stage'[Name], you can use a pattern like this (using Month 1 as an example):

Month 1 excluding Written from totals =
IF (
    ISINSCOPE ( 'D Stage'[Name] ),
    [Month 1],
    CALCULATE (
       [Month 1],
       KEEPFILTERS ( 'D Stage'[Name] <> "Written" )
    )
) 

As a side point, I would suggest that rather than writing one measure per Month Number, you instead place 'Date'[Month Number] as a Matrix Column field, and use a single [Sales Amount] or modified Sales Amount measure in the matrix.

If you use this method, you can use  a single measure like this:

Sales Amount excluding Written from totals =
IF (
    ISINSCOPE ( 'D Stage'[Name] ),
    [Sales Amount],
    CALCULATE (
       [Sales Amount],
       KEEPFILTERS ( 'D Stage'[Name] <> "Written" )
    )
) 

Regards,

Owen

[WinterMist]

@OwenAuger & @wdx223_Daniel - Thank you both for your solutions!

@OwenAuger - Thank you very much for taking the time to educate me.  I will need to spend more time studying your explanation.

Regards,

Nathan

[WinterMist]

@OwenAuger 

Your solution works (and I will mark it so), but there's something I do not understand.

Your solution has 2 parts:

1) The Pattern

2) Using a single measure instead of a separate measure for every month.

My confusion has only do with Part 1 "The Pattern".

For Part 1, your solution includes 2 separate measures,

Measure 1) [Month 1] (The measure I already had)

Measure 2) Your new measure with ISINSCOPE & KEEPFILTERS, which calls Measure 1.

As long as I keep 2 separate measures, it works!

However, as is common, I prefer to have only 1 measure instead of 2, if one of the measures has no purpose on its own.  (i.e. I have no desire to call [Month 1] on its own.  I only want to use it within the context of your measure.)

The following TWO-MEASURE solution works...

The following ONE-MEASURE solution does not work...

Can you help me understand why the 2 Measure solution works and why the 1 Measure solution does not?

(Whether the code for [Month 1] is in a separate measure or placed in a VAR called "Step1" within the same measure, it's the exact same code either way.  So how can there be a different result?)

Thanks for the help!

Nathan

P.S. Interestingly, the solution from @wdx223_Daniel works both ways (2 separate measures or 1 single measure).  I will also mark that a solution as well.  I just want to understand better first.

[OwenAuger]

Hi again @WinterMist 

Glad it was of some help 🙂

One small note on ISINSCOPE vs ISFILTERED:

• ISINSCOPE function detects whether the specified column is a grouping column, in this case detecting whether the current "level" of the matrix includes 'D Stage'[Name].
• ISFILTERED detects whether any filters have been applied to the specified column (or table). This will return TRUE in some cases when it would not be desirable in your case, e.g. if some filters have been applied to 'D Stage'[Name] by a slicer.

On your question on why the 2M and 1M measures are not equivalent:

The key point is that DAX variables store values (scalar values or table values), not expressions. When a variable is declared with VAR, the expression is evaluated in the current context and stored as a static value. Since a variable does not store a DAX expression, declaring a variable is not the same as declaring a measure.

For this reason, if VariableName is a variable, it never makes sense to write this:

CALCULATE (
    VariableName,
    ...
)

since this will always evaluate to VariableName.

In your example, the immediate reason that 1M measure doesn't work as intended is that the CALCULATE expression beginning on line 8 has no effect in modifying Step1 (since Step1 is a variable), with the result that Step2 = Step1.

There are different ways to rewrite the measure, but here is one option (if you still want to have the Month Number filter as part of the measure):

1M - Owen Month 1 =
CALCULATE (
    IF (
        ISINSCOPE ( 'D Stage'[Name] ),
        [Sales Amount],
        CALCULATE (
            [Sales Amount],
            KEEPFILTERS ( 'D Stage'[Name] <> "Written" )
        )
    ),
    'Date'[Month Number] = 1
)

Regards,

[wdx223_Daniel · Accepted solution]

=IF(ISFILTERED('D Stage'[Name]),[Sales Amount],CALCULATE([Sales Amount],'D Stage'[Name]<>"Written"))

[OwenAuger · Accepted solution]

Hello Nathan,

To exclude 'D Stage'[Name] = "Written" for any totals at a higher level than 'D Stage'[Name], you can use a pattern like this (using Month 1 as an example):

Month 1 excluding Written from totals =
IF (
    ISINSCOPE ( 'D Stage'[Name] ),
    [Month 1],
    CALCULATE (
       [Month 1],
       KEEPFILTERS ( 'D Stage'[Name] <> "Written" )
    )
) 

As a side point, I would suggest that rather than writing one measure per Month Number, you instead place 'Date'[Month Number] as a Matrix Column field, and use a single [Sales Amount] or modified Sales Amount measure in the matrix.

If you use this method, you can use  a single measure like this:

Sales Amount excluding Written from totals =
IF (
    ISINSCOPE ( 'D Stage'[Name] ),
    [Sales Amount],
    CALCULATE (
       [Sales Amount],
       KEEPFILTERS ( 'D Stage'[Name] <> "Written" )
    )
) 

Regards,

Owen