Forum Discussion
Comparing monthly aggregated data from one table to monthly targets in another table
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.
Resolver VHi 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!