cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## DAX - Exclude values from row total

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] ),
)
VAR costs =
CALCULATE (
SUM ( 'table'[amount] ),
)
RETURN
IF (
&& 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.
1 ACCEPTED SOLUTION
Super User

@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 (
CALCULATE ( SUM ( 'table'[amount] ) );
income - costs
);
IF (
CALCULATE ( SUM ( 'table'[amount_open] ) );
income_open - costs_open
)
)```
16 REPLIES 16
Super User

Hi @MiKeZZa

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

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

Post Patron

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:

columngroup
column
subcolumn
detail

Post Patron

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.

Super User

@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?

Post Patron

@AlB

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

Super User

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?

Post Patron

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] );
)
VAR costs =
CALCULATE (
SUM ( 'table'[amount] );
)

RETURN
IF (
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.

Post Patron

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

Super User

Hi @MiKeZZa

```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
)```
Super User

@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
) ```

Post Patron

@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

Super User

@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 (
CALCULATE ( SUM ( 'table'[amount] ) );
income - costs
);
income_open - costs_open
)```
Post Patron

@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.

Super User

@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 (
CALCULATE ( SUM ( 'table'[amount] ) );
income - costs
);
IF (
CALCULATE ( SUM ( 'table'[amount_open] ) );
income_open - costs_open
)
)```
Post Patron

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!

Super User

@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
)```