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

common763's avatar
common763
Helper 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.

 

common763's avatar
common763
Helper III
6 years ago

Lionel you are a genius.  This worked.  I have to stress I think the main area of issue was that I had TIME Intelligence checked in options.  Once I unchecked everything worked.  Thanks so much!!!!