Create a measure between fields from a raw data table and a calculated table

[LX]

Hi, I am trying to create a measure between my table 2 (raw data) and pivot table 1 (calculated from raw data table 1) in Power BI. Ideally I want get a measure = monthly sales - monthly quota. In this case, since my raw data table 1 (column A) is organized at daily level, I need to aggregate it to monthly level so that I can compare monthly sales with monthly quota (organized at monthly level directly). 

Could anyone advise what steps I should follow to achieve this in Power BI?

Many thanks!

LX

[v-jiascu-msft]

Hi @LX,

Could you please tell me if your problem was resolved? Please mark the proper answer if it worked. That will be a help to the others.

Best Regards!
Dale

[LX]

Hi @v-jiascu-msft

Thank you! It worked and helped a lot!

LX

[v-jiascu-msft · Accepted solution]

Hi @LX,

First, we need a Date table to bring sales and quota together. Establish relationship later.

Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) )

Second, we create a measure.

Measure =
SUM ( Sales[Sales] ) - SUM ( Quota[Quota] )

Third, create a visual (table). (pay attention to where the fields in the visual come from)

Best Regards!

Dale