Lookup values from another table, with conditions, and display the values on only selected rows

[Angie_PowerBI]

Hi all,

I have 2 Fact tables below. I want 2 new columns as in red in the Sales table.

The Tracking sales $ column consists the Tracking sales values, based on the Materials from the Tracking sales table, and should have data for the Total row only, regardless of how many additional columns (e.g. city, state etc in addition to the Customers column) I'll add to the table.

The Differences $ column is the difference between the Sales $ and Tracking sales $, but the Total sales for a material, for example, Material 1 should exclude the negative sales -$1 from Customer B. Therefore, the Differences $ for Material 1 is $1, not $0.

Please help ! I've been struggling with this for a week. Thank you so much!

Sales: due to refund, the sales can be negative.

Materials	Customers	Cities	Sales $	Tracking sales $	Differences $
1	Total		1	1	1
	A	Auburn	2
	B	Birmingham	-1
2	Total		8	5	3
	A	Auburn	3
	C	Clanton	5
3	Total		21	0	21
	A	New York	6
	D	Decatur	8
	E	Los Angeles	7

Tracking sales: sales as a result of some programs

Materials	Tracking sales $
1	1
2	5

Edit: I added more materials and also more columns (Cities) to the Sales table, to indicate that the Sales table is the Master fact table and bigger than the Tracking Sales table.

[Jihwan_Kim]

Hi,

please check the below picture and the attached pbix file.

all measures are in the attached pbix file.

the below is for creating a matrix visualization.

Sales: =

IF ( HASONEVALUE ( Tracking_sales[Materials] ), SUM ( Sales[Sales] ) )

Tracking sales: =

IF (

AND (

HASONEVALUE ( Tracking_sales[Materials] ),

NOT HASONEVALUE ( Customers[Customers] )

),

SUM ( Tracking_sales[Tracking sales] )

)

Differences: =

IF (

AND (

HASONEVALUE ( Tracking_sales[Materials] ),

NOT HASONEVALUE ( Customers[Customers] )

),

CALCULATE (

[Sales:],

FILTER ( ALLEXCEPT ( Sales, Tracking_sales[Materials] ), Sales[Sales] > 0 )

) - [Tracking sales:]

)

[Angie_PowerBI]

Thanks @Jihwan_Kim . Really appreciate your quick help. Your method works great but if the Sales table has more materials than the Tracking sales table, the visual that is based on the Tracking sales table, instead of the Sales table, results in the missing info like below.

I'm not sure if my Sales table is too big. Following your method of building the visual and table relationships, all the Customers, whether or not there are sales for that customer under that materials, are added to the Materials (only Customer 10 and 11 have the sales). It's not a problem for me because I can filter out the blank of Customer sales. However, when I add cities, for example, to the visual, similiar process happens that all the cities are added under each of the full list of customers. The visual takes a long time to load.

Appreciate alternative ways if you have, @Jihwan_Kim . Thanks so much!

[Jihwan_Kim]

Hi,

Thank you for your feedback.

May I have your sample data? 

You can try to upload your sample data to your Onedrive, Googledrive, or Dropbox, and then you can share the link here.

Thanks.

[Angie_PowerBI]

Hi @Jihwan_Kim , 

I'd need your email address to create the link to my sample data on OneDrive. I'm using my work computer.

Anyway, don't worry about the blank data rows. I'll figure it out once you can help me to have the Material 3 appear on the visual.

Thanks so much again !

[Ashish_Mathur]

Hi,

Do you want this as a measure or as a calculated column formula?  Share the link from where i can download your PBI file.

[Angie_PowerBI]

Hi @Ashish_Mathur ,

Any method (measures or calculated columns or both) that can give me the table like below is great. Again, the 2 new columns are Tracking sales and Differences. I edited the pbix file from Jihwan_Kim and can send it to you but I don't have a way to share the link from my work computer. Thanks!

[Ashish_Mathur]

I'll request Jihwan Kim help you with this.