Forum Discussion

MiKeZZa's avatar
MiKeZZa
Icon for Post Patron rankPost 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
Icon for Community Champion rankCommunity Champion
7 years ago

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

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's avatar
    MiKeZZa
    Icon for Post Patron rankPost 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!

  • MiKeZZa's avatar
    MiKeZZa
    Icon for Post Patron rankPost Patron
    7 years ago

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

    MiKeZZa

    Nth iteration :smileyvery-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
    )
  • MiKeZZa's avatar
    MiKeZZa
    Icon for Post Patron rankPost Patron
    7 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity 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
        )
    )
  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity 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
    )