Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
common763
Helper II
Helper II

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

1 ACCEPTED SOLUTION

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
)

 

v-lionel-msft_0-1597395093242.png
 

 

v-lionel-msft_1-1597395117098.png

 

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.

View solution in original post

8 REPLIES 8
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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
)

 

v-lionel-msft_0-1597395093242.png
 

 

v-lionel-msft_1-1597395117098.png

 

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.

Thanks for this Lionel. We are using this to show revenue change over import dates in our production schedule, and I am wondering how I can edit this formula to allow additional filtering by both import date, and planned end date. 

 

The difference formula works when filtering by one column "import date":

abartell_0-1683577194140.png

But when I try to filter by the month end date, it gives me this:

abartell_1-1683577356233.png

 

Is there another clause I need to add to allow filtering by both import date and planned end date? 

abartell_2-1683577428302.png

 

 

 

 

 

 

Anonymous
Not applicable

Hi Lionel,

 

Thank you, this was really helpful, However for some fields the calculation is wrong. How this can be solved. 

And is there any Measure to calacute the difference without the dependence of Rows.

Would be great if you could help me on this please, much appreciated, thank you.

Note: I have Week number in Column, and when I filter/slicer 2 weeks the difference should calculate only for that two columns and not for the entire columns.

 

Sreepathi_1-1631456949428.png

Regards,

Sreepathi K

Hi,

 

Great solution! 

 

How should I change the code if the are more columns and if I want to have the difference value calculated correctly also if a column is expanded or collapsed. For example:

 

FacilityGroupFacility01/01/202001/05/2020
1A10050
1B50100
2C050

 

KR,

 

Lars

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.

 

SAMPLE.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors