March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
Drag both [Week Num] and [Category] from the one side of the relationship.
The relationships could be complex. You may try using DAX to add a calculated table.
I am running in DirectQuery mode so that option is greyed out.
Drag both [Week Num] and [Category] from the one side of the relationship.
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.
ID | Year | WeekNum | Category | RevenueTarget |
41Fruit | 2017 | 41 | Fruit | 1010 |
41Veg | 2017 | 41 | Veg | 998 |
41Beer | 2017 | 41 | Beer | 1005 |
41Meat | 2017 | 41 | Meat | 480 |
41Soda | 2017 | 41 | Soda | 501 |
41Sweets | 2017 | 41 | Sweets | 499 |
41Snacks | 2017 | 41 | Snacks | 472 |
42Fruit | 2017 | 42 | Fruit | 1010 |
42Veg | 2017 | 42 | Veg | 998 |
42Beer | 2017 | 42 | Beer | 1005 |
42Meat | 2017 | 42 | Meat | 480 |
42Soda | 2017 | 42 | Soda | 501 |
42Sweets | 2017 | 42 | Sweets | 499 |
42Snacks | 2017 | 42 | Snacks | 472 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
85 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |