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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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?

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

    AlB

     

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

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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?

       

             

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

        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] );
        '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.