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
bematth
Helper I
Helper I

Subtracting one column of a matrix from another and display result

bematth_2-1660752939513.png

I have a matrix with two date columns and need to display the difference between the two. So the result would look like:

bematth_1-1660752894310.png  

 

So I start by creating two measures: 

bematth_4-1660753460243.pngbematth_5-1660753477179.png

And then created 

bematth_6-1660753708182.png

I'm not able to add this as a column to the existing matrix, so I've tried things like creating second matrix or table and hiding the row and column names so it looks like one visual 

 

But no matter what type of visual or how I arrange variables, I can't get the visual to generate.

bematth_7-1660754280209.png

Any suggestions?

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@bematth 

I think the issue is here:

delete the quotations ( " " ) keep only the number

1A040884-889B-40E3-B9F5-548839EBCE3E.jpeg

View solution in original post

14 REPLIES 14
v-yalanwu-msft
Community Support
Community Support

Hi, @bematth ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to close the case. 

If not,

You could change it to:

nf2020=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Year]=2020))
nf2021=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Year]=2021))
Difference = [nf2020] - [nf2021]


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft 

this issue has been solved already. Thank you

tamerj1
Super User
Super User

@bematth 

I think the issue is here:

delete the quotations ( " " ) keep only the number

1A040884-889B-40E3-B9F5-548839EBCE3E.jpeg

@bematth 

The above reply ☝️

Aha! That did it!

I swear, 9/10 my error is usally the result of something like that!

Thank you!

tamerj1
Super User
Super User

Hi @bematth 
Difference = [nf2020] - [nf2021]

It is also worth mentioning that it is a good practice to have a date table filtering your fact table.

I get the same results when using Difference = [nf2020] - [nf2021].

 

Based on the data source, I'm not sure a date table would be a possibility. 

@bematth 

Usually you can create a date table locally and connect it to your fact table. However, what do you mean by same result? You receive an error? What does the error say? Are you sure nf2020 and nf2021 are measure?

I get the same "Cannot display visual... comparison operators do not support comparing values of type integer with vlalues of type text..." error.

nf2020 and nf2021 are measures with the data type being whole number, as is nfdiff.  (And using VALUE didn't work the first time)

 

 

bematth_0-1660756764727.png

 

@bematth 

Would you please return the two measure alone without the difference and share the results.

As in just pull them into the visual instead? I get the same error:

bematth_0-1660757554907.png

 

@bematth 

Please see my last reply with the proposed solution. 

I thought I just did? I posted a screen shot as well. 

Hi @bematth !

 

I believe the problem resides in your year column.

You try to filter a text value "2020" with a numeric column.


Either change the type of year column to text.

Or change your filter to 2020 instead of  "2020".

 

Could you try it out and let us know if it worked?

 

Hope it helped!

 

Kind regards,

OD

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.