Forum Discussion

common763's avatar
common763
Icon for Helper III rankHelper 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...
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.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Community Support rankCommunity 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.

      • common763's avatar
        common763
        Icon for Helper III rankHelper III
        6 years ago

        Lionel:

        I dont understand what I can be doing wrong here.  I followed your calculation to a tee and it is showing just one date and a total if I selected two dates in the filter.    If I have all the dates selected it sums that last date.  I am really at my wits end with this because I cannot believe how difficult this has been.  All I want is to display DATES in Column, Facilities in ROW and to calculate the SUM of number under each date column with the DIFFERENCE shown next to TOTAL.  The end-user for this report will be selecting two dates from either a slicer or filter and wants to see the totals for the date, and then the difference btw the two. 

         

        Here is a screen.