Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
User | Count |
---|---|
77 | |
76 | |
43 | |
30 | |
24 |
User | Count |
---|---|
97 | |
91 | |
52 | |
47 | |
46 |