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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
matlawrence
Frequent Visitor

Compare calculated field to non-calculated field

Hi, I have a newbie problem when trying to calculate a variance between a summed field from one table compared to a value in another table. Here is what happens... Create a table visualization Pull in the following data

Week Num from table A

Category from Table B

Revenue from Table C (this is at product level so I sum by Week Num & Category I get these results

 

Week Num Category Revenue

41               Fruit        1,070.00

41               Veg           196.90

42              Fruit         1,080.25

42              Veg            304.60

 

Next I add another table called Targets which contains RevenueTarget values by Week Num/Category as values so not summed. This table is joined to the Revenue Table C on a uniqueID in both which is concatenate(Week Num, Category)

Week Num Category RevenueTarget

41               Fruit        1,005.00

41               Veg            480.00

42               Fruit        1,005.00

42               Veg            480.00

 

I get the desired results showing in my table visualization...

Week Num Category Revenue RevenueTarget

41               Fruit        1,070.00 1,005.00

41               Veg            196.90    480.00

42               Fruit        1,080.25 1,005.00

42               Veg            304.60    480.00

 

Now here's the problem. I want to show the variance between Revenue vs RevenueTarget so I tried adding a new Measure RevenueVar = AVERAGE([RevenueTarget]) - SUM(Table C[Revenue]) which I got from a Community post to someone else as being the way to compare calculated to non-calculated fields. The results were not what I had hoped for and took a very long time to bring the data back...

Week Num Category Revenue RevenueTarget RevenueVar

41                                                 0.00                    0.00

41                                             472.00                472.00

41                                             480.00                480.00

41                                          1,005.00              1,005.00

plus lots more repeating rows per Week Num/Category

Any help would be much appreciated. Thank you.

1 ACCEPTED SOLUTION

@matlawrence,

 

Drag both [Week Num] and [Category] from the one side of the relationship.

Community Support Team _ Sam Zha
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

7 REPLIES 7
v-chuncz-msft
Community Support
Community Support

@matlawrence,

 

The relationships could be complex. You may try using DAX to add a calculated table.

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

I am running in DirectQuery mode so that option is greyed out. 

@matlawrence,

 

Drag both [Week Num] and [Category] from the one side of the relationship.

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

You are a genius! Thank you so much as this has been driving me mad.

Does anyone else have any pointers on how to solve this issue? I am sure I can't the only person using Power BI that needs to have targets imported and compared to sales performance.

Thanks in advance.

@matlawrence you explain the problem well but it will be nice if you provide sample data of all tables in excel sheet , will help to come with solution



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

IDYearWeekNumCategoryRevenueTarget
41Fruit201741Fruit1010
41Veg201741Veg998
41Beer201741Beer1005
41Meat201741Meat480
41Soda201741Soda501
41Sweets201741Sweets499
41Snacks201741Snacks472
42Fruit201742Fruit1010
42Veg201742Veg998
42Beer201742Beer1005
42Meat201742Meat480
42Soda201742Soda501
42Sweets201742Sweets499
42Snacks201742Snacks472

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.