Forum Discussion

Nijlal01's avatar
Nijlal01
Helper I
5 years ago
Solved

Total as difference on multiple levels

Hi all,   I am trying to display the total as difference. Unfortunatly I do not get it to work on al levels. Anyone knows how to rewrite the DAX formula in order to display difference correctly?  ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Nijlal01 

    I have a test by your sample and dax code. I find that your use AllSELECTED and ALL function in your X and Y code. Due to you use hierachy level in your Matrix, I think you don't need to use these two function to get data. But you need to use ALL function to get Max Date.

    Update Code:

    Difference =
VAR _MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[PublishDate] )
VAR _x =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] < _MaxDate )
    )
VAR _y =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] = _MaxDate )
    )
RETURN
    IF ( HASONEFILTER ( Sheet1[PublishDate] ), SUM ( Sheet1[QTY] ), _x - _y )

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Nijlal01's avatar
Nijlal01
Helper I
4 years ago

I did try to use the post Greg_Deckler. Unfortunatly I did not get it to work 😞 There are more post about displaying total as difference however not one which explains how to do it on multiple levels. And I am to newbie to figure it out myself.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Nijlal01 Can you post your sample data?

  • Nijlal01's avatar
    Nijlal01
    Helper I
    4 years ago

    Hi Greg_Deckler ,

     

    Thanks for the help! See data below:

     

    PublishDate

    		ProductShipToWkNrQTY
    1-1-2021100001A110
    1-1-2021100001B115
    1-1-2021100001B210
    1-1-2021100002A115
    1-1-2021100002A220
    1-1-2021100002B220
    2-1-2021100001A115
    2-1-2021100001A125
    2-1-2021100001B220
    2-1-2021100002B215
    2-1-2021100002A230
    2-1-2021100002A110
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Nijlal01 

      I have a test by your sample and dax code. I find that your use AllSELECTED and ALL function in your X and Y code. Due to you use hierachy level in your Matrix, I think you don't need to use these two function to get data. But you need to use ALL function to get Max Date.

      Update Code:

      Difference =
VAR _MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[PublishDate] )
VAR _x =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] < _MaxDate )
    )
VAR _y =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] = _MaxDate )
    )
RETURN
    IF ( HASONEFILTER ( Sheet1[PublishDate] ), SUM ( Sheet1[QTY] ), _x - _y )

      Result is as below.

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Nijlal01's avatar
        Nijlal01
        Helper I
        4 years ago

        You are a genius! Thanks 😄