Forum Discussion

common763's avatar
common763
Helper III
6 years ago
Solved

Difference instead of Total in PowerBI Matrix

I cant seem to find a solution to this online.  Basically I have a matrix table that displays two columns and instead of showing the total all I want is to display the difference.  Not sure how to do this.  If I create a measure it doesnt display properly although I know I am doing it wrong.   

 

1/1/2020         1/2/2020        DIFFERENCE

1                      5                     4

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi common763 ,

     

    Please create such a measure.

     

    Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet4[Number]),
    FILTER(
        ALLSELECTED(Sheet4),
        Sheet4[DATE] < MAX(Sheet4[DATE]) && Sheet4[FACILITY] = MAX(Sheet4[FACILITY])
    )
)
VAR y = 
CALCULATE(
    SUM(Sheet4[Number]),
    FILTER( ALL(Sheet4), Sheet4[DATE] = MAX(Sheet4[DATE]) && Sheet4[FACILITY] = MAX(Sheet4[FACILITY]) )
)
RETURN
IF(
    HASONEFILTER(Sheet4[DATE]),
    SUM(Sheet4[Number]),
    x-y
)

     

     

     

     

    Best regards,
    Lionel Chen

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

8 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    common763 

    As per my understanding of your question, you want to keep only the column with the difference. you can create a measure for the difference and drop it in the value section. What is the issue with it?

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

    • common763's avatar
      common763
      Helper III
      6 years ago

      How would you write the measure?  I want to do the following and am doing something wrong.   When I add a measure it is adding a difference for every date.  I just want it to show like the total does and then remove the total because it is irrelevant in the metric. 

       

      ROWS-FACILITY

      COLUMNS-DATE

      VALUES-NUMBER

       

      FACILITY1/1/20201/5/2020DIFF
      AAA1005050
      BBB50100-50
      CCC050-50
      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi common763 ,

         

        Please create such a measure.

         

        Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet4[Number]),
    FILTER(
        ALLSELECTED(Sheet4),
        Sheet4[DATE] < MAX(Sheet4[DATE]) && Sheet4[FACILITY] = MAX(Sheet4[FACILITY])
    )
)
VAR y = 
CALCULATE(
    SUM(Sheet4[Number]),
    FILTER( ALL(Sheet4), Sheet4[DATE] = MAX(Sheet4[DATE]) && Sheet4[FACILITY] = MAX(Sheet4[FACILITY]) )
)
RETURN
IF(
    HASONEFILTER(Sheet4[DATE]),
    SUM(Sheet4[Number]),
    x-y
)

         

         

         

         

        Best regards,
        Lionel Chen

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