Forum Discussion

MiKeZZa's avatar
MiKeZZa
Post Patron
7 years ago
Solved

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...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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
        )
    )
AlB's avatar
AlB
Community Champion
7 years ago

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's avatar
MiKeZZa
Post Patron
7 years ago

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's avatar
    AlB
    Community Champion
    7 years ago

    MiKeZZa

    Cool :smileyvery-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
    )