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

16 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi MiKeZZa

     

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

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

    • MiKeZZa's avatar
      MiKeZZa
      Post Patron
      7 years ago

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

        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.