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.

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.

 

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