DAX - Exclude values from row total

[MiKeZZa]

I want to exclude some values from my total on a row.

I've tried some things and I have the feeling that I'm almost there. As you can see I've excluded the red cells from the total in purple, but I didn't want that. I wanted to exclude it from orange. Is this possible?

My current dax:

totalamount =
VAR income =
    CALCULATE (
        SUM ( 'table'[amount] ),
        'table'[header] = "income"
    )
VAR costs =
    CALCULATE (
        SUM ( 'table'[amount] ),
        'table'[header] = "costs"
    )
RETURN
    IF (
        MIN ( table[header] ) <> MAX ( table[header] )
            && SUM ( date_table[period_closed] ) < 1,
        0,
        income - costs
    )

The date_table[period_closed] is the field that is used to determine to count or not to count in total and is also used for the conditional formatting which gives the blue background.

[AlB]

Hi @MiKeZZa

What is on the rows of yor matrix? What fields? 

Can you share the file (with dummy data if necessary)?

[MiKeZZa]

Hi @AlB,

Haven't found a way to easy mask some data so if it's really necessary I'll make it tonight. But that will cost me about an hour I guess.

On the rows is:

header
columngroup
column
subcolumn
detail

[MiKeZZa]

I've been able to make a simple and small example. Can be downloaded here: https://ufile.io/3o1tu

I've put the DAX back to an earlier version; be aware that costs ánd income are positive but at the end they are abstracted.

[AlB]

@MiKeZZa

Let's see if I understand what you need. In the orange area you would want the sum of all the columns except the columns in the red area?

[MiKeZZa]

@AlB

Exactly. That's what I'm looking for.

[AlB]

Hey @MiKeZZa

At the row subtotals (orange area) you do not have information on what periods are closed because the [period_closed] field is in the columns.

I'm thinking you're going to need an extra column in your Sales table:

[Amount_Open]=[Amount]*(1 - [Period_closed]).

In the data model you've shared, you'd be getting the [Period_closed] value through RELATED( ). [Amount_Open] would thus have zeros in the periods that are closed, and just [Amount] in those not closed

Once we have that, you can use the [Amount] column for your SUM( ) everywhere except at the row subtotals, where you'd be using SUM([Amount_Open]). You can detect when the measure is at the row subtotals with something like what you have shown in your measure for the column subtotal:

MIN ( 'table'[yearmonth] ) <> MAX ( 'table'[yearmonth] )

There are probably more elegant ways to do it, though. I ain't had time to test it.

What do you think?

[MiKeZZa]

Hi @AlB

I've tried some things like what you've said. PBIX is here: https://ufile.io/6ah99

Output is this:

On the left side the original. On the right the new try based on these DAX:

totalamount_open = 
VAR income =
    CALCULATE (
        SUM ( 'table'[amount] );
        'table'[header] = "income"
    )
VAR costs =
    CALCULATE (
        SUM ( 'table'[amount] );
        'table'[header] = "costs"
    )

  RETURN
    IF (
        HASONEFILTER ('table'[header] );
        CALCULATE ( SUM ( 'table'[Amount_Open] ) );
        income - costs
    )

Blue is not ok; must be filled. Green is good; values are good. And orange is wrong; the last month must be excluded from this value.

[MiKeZZa]

A little bump; problem still exists.... Anyone any idea? 

[AlB]

Hi @MiKeZZa

Had forgotten about this. Have you tried the following? Let me know

totalamount_open =
VAR income =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "income" )
VAR costs =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "costs" )
VAR income_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "income" )
VAR costs_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "costs" )
RETURN
    IF (
        MIN ( 'table'[yearmonth] ) <> MAX ( 'table'[yearmonth] );
        income_open - costs_open;
        income - costs
    )

[AlB]

@MiKeZZa

Review of the previous. We need to use date_table[yearmonth]   rather than   table[yearmonth]

totalamount_openB = 
VAR income =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "income" )
VAR costs =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "costs" )
VAR income_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "income" )
VAR costs_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "costs" )
RETURN
    IF ( ISFILTERED(date_table[yearmonth] );     
        income - costs;
        income_open - costs_open
    ) 

[MiKeZZa]

@AlB

Thank you for your support. We're still getting a bit closer. You've made it a little better, but it's good really good at this moment. See what's marked green (good) and red (not ok):

To make it a little easier for you I've posted the PBIX in a previous post. Here it is again, but now with your latest dax included: https://ufile.io/7iunr

[AlB]

@MiKeZZa

Nth iteration

totalamount_openC = 
VAR income =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "income" )
VAR costs =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "costs" )
VAR income_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "income" )
VAR costs_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "costs" )
RETURN
    IF (
        ISFILTERED ( date_table[yearmonth] );
        IF (
            ISFILTERED ( 'table'[header] );
            CALCULATE ( SUM ( 'table'[amount] ) );
            income - costs
        );
        income_open - costs_open
    )

[MiKeZZa]

@AlB

No, no success at all. The vertical totals are incorrect.

As said; when it's more easy for you you can try it directly yourself in my PBIX.

[AlB · Accepted solution]

@MiKeZZa

Well, "no success at all" sounds like a bit of an exaggeration... 

(N+1)th version.  I had posted this already immediately after my latest post as I realised there was an error.  But for some reason the post has disappeared. Here it is again

totalamount_openD = 
VAR income =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "income" )
VAR costs =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "costs" )
VAR income_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "income" )
VAR costs_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "costs" )
RETURN
    IF (
        ISFILTERED ( date_table[yearmonth] );
        IF (
            ISFILTERED ( 'table'[header] );
            CALCULATE ( SUM ( 'table'[amount] ) );
            income - costs
        );
        IF (
            ISFILTERED ( 'table'[header] );
            CALCULATE ( SUM ( 'table'[amount_open] ) );
            income_open - costs_open
        )
    )

[MiKeZZa]

How funny. My WiFi was broken so I didn't was able to check for a new post and I've tried to solve it on my own. I came up to the exact same solution like you post now! That works great indeed. Very very very much appriciated!

[AlB]

@MiKeZZa

Cool

Here another version, perhaps a bit more maintainable. Matter of preference.

totalamount_openD2 =
VAR income =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "income" )
VAR costs =
    CALCULATE ( SUM ( 'table'[amount] ); 'table'[header] = "costs" )
VAR income_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "income" )
VAR costs_open =
    CALCULATE ( SUM ( 'table'[amount_open] ); 'table'[header] = "costs" )
VAR IsGrandTotal =
    NOT ( ISFILTERED ( date_table[yearmonth] ) )&& NOT ( ISFILTERED ( 'table'[header] ) )
VAR IsRowTotal =
    NOT ( ISFILTERED ( date_table[yearmonth] ) ) && ISFILTERED ( 'table'[header] )
VAR IsColumnTotal =
    ISFILTERED ( date_table[yearmonth] ) && NOT ( ISFILTERED ( 'table'[header] ) )
VAR IsNotTotal =
    ISFILTERED ( date_table[yearmonth] ) && ISFILTERED ( 'table'[header] )
RETURN
    SWITCH (
        TRUE ();
        IsNotTotal; CALCULATE ( SUM ( 'table'[amount] ) );
        IsRowTotal; income - costs;
        IsColumnTotal; CALCULATE ( SUM ( 'table'[amount_open] ) );
        IsGrandTotal; income_open - costs_open
    )