Compare calculated field to non-calculated field

[matlawrence]

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.

[v-chuncz-msft]

@matlawrence,

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

[matlawrence]

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

[v-chuncz-msft · Accepted solution]

@matlawrence,

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

[matlawrence]

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

[matlawrence]

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.

[parry2k]

@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

[matlawrence]

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