Forum Discussion
Comparing monthly aggregated data from one table to monthly targets in another table
Hi there,
I'm relatively new to Power BI but learning!
I have two sets of data which I want to link together, but I'm struggling to get to exactly what I want.
In one table I have donation amount and dates, and in another I have the monthly target for total donations.
I want to show in one chart how we're doing actual vs. target (see examples below).
Any help would be massively appreciated.
Cheers,
Nic
Table 1
| Donation Amount | Donation Date |
| 5 | 15/04/17 |
| 10 | 23/04/17 |
| 20 | 01/05/17 |
| 10 | 18/05/17 |
| 5 | 02/06/17 |
| 7 | 30/06/17 |
Table 2
| Month | Target |
| Apr-17 | 20 |
| May-17 | 30 |
| Jun-17 | 12 |
Desired outcome
| Month | Actual Donation Total | Target Donation Total | Difference |
| Apr-17 | 15 | 20 | -5 |
| May-17 | 30 | 30 | 0 |
| Jun-17 | 12 | 15 | -3 |
Hi NManku, this is a typical many-to-1 scenario. You would begin (in Data View) by adding a calculated column to Table 1 that transforms the donation date-month-year into month-year, which is the same format and data type as Table 2's "month" column.
For example, if Table 2's "month" column is a "text" type, then the calculated column should be text as well. However, if it's a date type that uses 1-Apr-2017 to represent the whole month, then the calculated column should map DD-Apr-2017 to 1-Apr-2017.
Next, in the Relationship View, link this calculated column to the "month" column in Table 2.
Then, you'd add three measures :
Actual Donation Total := SUM( 'Table 1'[Donation Amount] )
Target Domation Total := AVERAGE('Table 2'[Target] )
Difference := [Actual Donation Total] - [Target Donation Total]
Finally, go to Report View, add a Table visual, and put "Month" from Table 2 on the rows, and the three measures on the Values.
2 Replies
- DAX0110
Resolver V
Hi NManku, this is a typical many-to-1 scenario. You would begin (in Data View) by adding a calculated column to Table 1 that transforms the donation date-month-year into month-year, which is the same format and data type as Table 2's "month" column.
For example, if Table 2's "month" column is a "text" type, then the calculated column should be text as well. However, if it's a date type that uses 1-Apr-2017 to represent the whole month, then the calculated column should map DD-Apr-2017 to 1-Apr-2017.
Next, in the Relationship View, link this calculated column to the "month" column in Table 2.
Then, you'd add three measures :
Actual Donation Total := SUM( 'Table 1'[Donation Amount] )
Target Domation Total := AVERAGE('Table 2'[Target] )
Difference := [Actual Donation Total] - [Target Donation Total]
Finally, go to Report View, add a Table visual, and put "Month" from Table 2 on the rows, and the three measures on the Values.
AimeeclairdHelper IV
Hi DAX0110
This thread has been really helpful to me as i'm trying to solve a similar requirement.
There is a slight difference in my data in that I have a variety of different contracts, each sale was completed under a contract and i'd like to lookup the target value for that contract. For example;
Target table =
Contract Target Value Target Period 1 1000000 Jan 2020 2 1500000 Feb 2020 3 2000000 March 2020 1 500000 Jan 2020 2 1000000 Feb 2020 3 1750000 March 2020 1 2500000 Jan 2020 2 2000000 Feb 2020 3 1750000 March 2020 Sales Data =
Contract Sale Value Date of Sale 1 500000 01/01/2020 2 450000 17/01/2020 3 340000 22/03/2020 1 250000 4/02/2020 2 345000 5/03/2020 3 333000 12/03/2020 1 125670 30/03/2020 2 400000 30/01/2020 3 350000 15/03/2020 Please can you advise how I can adapt the solution you've provided below to also consider which contract we need to lookup?
Many thanks in advance!