Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am wondering - What would be the best approach to comparing 1 source of data (i.e. 1- actual results) to a second target data set (i.e. 2-target results), when both data sets are at different levels of granularity i.e. the '1 - actual results' will be at a transactional level timestamped and the '2- target results' will be at a monthly level?
Using direct quey - I have been able to pre-aggregate the '1-actual' results to match the granularity of the target data set and join these using key fields of a given attribute & Date - that is fine.
I have then attempted to create a common key field, in 1- actual results and in the pre-aggregated result set by combining the chosen attribute & 1st day of the month & and then using another table to create a relationship. This allows for a visual based on the monthly aggregated data to filter another visual that is based on transactional data...
However when using this approach - I have encountered some issues with the model when re-loading data.
If any direction or advice could be provided here that would be most appreciated.
Regards,
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @mleepin,
I'd like to suggest you to add calculated columns to stored unique 'team month' value and create a bridge table to link these tables.
Calculate columns:
MT = FORMAT(Actual[Date],"mmm-yy") &","&Actual[Team] MT = Target[Month] &","&Target[Team]
Calculate table:
Bridge = DISTINCT(UNION(VALUES(Actual[MT]),VALUES(Target[MT])))
Create relationships from actual[MT] to bridger[MT], target[MT] to bridge[MT].
After these steps, you can write measure to calculate percent of 'act vs tar':
Act vs Tar = SUM(Actual[Time Actual])/SUM(Target[Time Target Monthly])
Regards,
Xiaoxin Sheng
Hi @mleepin,
Can you please provide more detailed information about this?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Some e.g.s of data inputs (1 & 2) and desired outputs (3 & 4) are below:
1/ Actual results (transactional grain)
Team | Date | Time Actual |
A | 1/01/2018 | 1.5 |
A | 2/01/2018 | 2.2 |
A | 3/02/2018 | 2.4 |
A | 3/03/2018 | 3.2 |
A | 5/03/2018 | 2.5 |
B | 3/01/2018 | 1.6 |
B | 11/01/2018 | 1.9 |
B | 12/02/2018 | 2.2 |
B | 10/03/2018 | 3.1 |
B | 12/03/2018 | 2.9 |
2/ Target results (monthly grain), grouped by team
Team | Month | Time Target Monthly | Time Target YTD |
A | Jan-18 | 5 | 5 |
A | Feb-18 | 5 | 10 |
A | Mar-18 | 5 | 15 |
B | Jan-18 | 4 | 4 |
B | Feb-18 | 4 | 8 |
B | Mar-18 | 4 | 12 |
3/Desired Output - actual vs target (monthly grain)
Team | Month | Time Target Monthly | Time Actual | Actual Vs Target |
A | Jan-18 | 5 | 3.7 | 74.00% |
A | Feb-18 | 5 | 2.4 | 48.00% |
A | Mar-18 | 5 | 5.7 | 114.00% |
B | Jan-18 | 4 | 3.5 | 87.50% |
B | Feb-18 | 4 | 2.2 | 55.00% |
B | Mar-18 | 4 | 6 | 150.00% |
4/ Desired output - Drill down example or detailed visualisation example of actual transactions that can be filtered or navigated to from selecting a dat point in the prior visualisation in 3/ Above....
Team | Date | Time Actual |
A | 1/01/2018 | 1.5 |
A | 2/01/2018 | 2.2 |
Hi @mleepin,
I'd like to suggest you to add calculated columns to stored unique 'team month' value and create a bridge table to link these tables.
Calculate columns:
MT = FORMAT(Actual[Date],"mmm-yy") &","&Actual[Team] MT = Target[Month] &","&Target[Team]
Calculate table:
Bridge = DISTINCT(UNION(VALUES(Actual[MT]),VALUES(Target[MT])))
Create relationships from actual[MT] to bridger[MT], target[MT] to bridge[MT].
After these steps, you can write measure to calculate percent of 'act vs tar':
Act vs Tar = SUM(Actual[Time Actual])/SUM(Target[Time Target Monthly])
Regards,
Xiaoxin Sheng
Hi,
You may download my PBI file from here.
Hope this helps.
Apologies for the delay. Using this model has worked out. Thank You,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |