Calculate quantity based on average price and region

[Anonymous]

I'm trying to calculate fuel prices based on data from a separate table. I have two sources for fuel, Table 1 gives me detail of every transaction including Fuel Type, Pump Price, Litres, Transaction Date and Region where the sale took place. In Table 2 I get invoices that only include Fuel Type, Invoice $ Total, Region and Transaction Date. I'm trying to calculate the litres sold in Table 2 based on the average price in the region for the month, and if there are no sales that month, use the overall average.

I've created measures that give me the correct averages using the data from Table 1. I think I can get the correct answer by grouping and creating a new table from Table 1, but is it possible to do this using measures? 

Region	Oct	Nov	Dec	Jan	Feb	Average
Region 1	2.26	2.35	2.22	2.08		2.23
Region 2	2.21	2.31	2.17	2.02	2.06	2.15
Region 3	2.24	2.33	2.21	2.14	2.10	2.20
Region 4	2.26	2.32	2.21	2.16		2.24

Level 1	Invoice Amount	Date	Price to use	Desired Result
Region 1	$1,390.32	08/12/2022	2.22	626.3
Region 2	$251.64	08/12/2022	2.17	116.0
Region 3	$239.50	08/12/2022	2.21	108.4
Region 4	$1,110.05	28/11/2022	2.32	478.5
Region 1	$515.85	28/11/2022	2.35	219.5
Region 1	$261.11	02/02/2022	2.23	117.1
Region 2	$1,044.83	02/02/2022	2.06	507.2
Region 3	$109.25	02/02/2022	2.10	52.0
Region 4	$1,259.00	02/02/2022	2.24	562.1

[amitchandak]

@Anonymous , You need to unpivot the month in power bi, post that you will have a month

as you do not have a year

Try a date column like this in DAX

Date  =

var _date = datevalue("01-"&[Month] & "- " &Year(Today()) )

return

Date(if(month([Date]) <= month(Today()) , year(_date) , year(_date) -1) , month(_date) , 1)

You can use this to create a new column in table two

maxx(filter(Table1, Table1[region] = Table2[Region] && eomonth(Table1[Date],0) = eomonth(Table2[Date],0) ), Table1[Rate])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

The HTML tables didn't look right to me, so I'm reposting as images: