Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MiKeZZa
Post Patron
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?

 

almost_there.png

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.
1 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
        )
    )

View solution in original post

16 REPLIES 16
AlB
Super User
Super User

Hi @MiKeZZa

 

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

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

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

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.

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

@AlB

 

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

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?

 

       

Hi @AlB

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

 

Output is this:

 

Naamloos.png

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.

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

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
    )

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

 

 

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

 

Naamloos.png

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

@MiKeZZa

Nth iteration Smiley Very Happy

 

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
    )

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

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

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!

@MiKeZZa

Cool Smiley Very Happy

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
    )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.