Reply
Regular Visitor
Posts: 21
Registered: ‎05-03-2017
Accepted Solution

Comparing result sets at different levels of granularity

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,

 

 

 

 


Accepted Solutions
Super User
Posts: 3,944
Registered: ‎01-14-2017

Re: Comparing result sets at different levels of granularity

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

View solution in original post

Community Support Team
Posts: 7,499
Registered: ‎08-14-2016

Re: Comparing result sets at different levels of granularity

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].

22.PNG

 

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])

21.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post


All Replies
Community Support Team
Posts: 7,499
Registered: ‎08-14-2016

Re: Comparing result sets at different levels of granularity

Hi @mleepin,

 

Can you please provide more detailed information about this?

How to Get Your Question Answered Quickly

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Regular Visitor
Posts: 21
Registered: ‎05-03-2017

Re: Comparing result sets at different levels of granularity

Hi Xiaoxin Sheng,

 

 

Some e.g.s of data inputs (1 & 2) and desired outputs (3 & 4) are below:

 

 

1/ Actual results  (transactional grain)

TeamDateTime Actual
A1/01/20181.5
A2/01/20182.2
A3/02/20182.4
A3/03/20183.2
A5/03/20182.5
B3/01/20181.6
B11/01/20181.9
B12/02/20182.2
B10/03/20183.1
B12/03/20182.9

 

 

2/ Target results (monthly grain), grouped by team

TeamMonthTime Target MonthlyTime Target YTD
AJan-1855
AFeb-18510
AMar-18515
BJan-1844
BFeb-1848
BMar-18412

 

 

3/Desired Output -  actual vs target (monthly grain)

TeamMonthTime Target MonthlyTime ActualActual Vs Target
AJan-1853.774.00%
AFeb-1852.448.00%
AMar-1855.7114.00%
BJan-1843.587.50%
BFeb-1842.255.00%
BMar-1846150.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....

 

 

 

TeamDateTime Actual
A1/01/20181.5
A2/01/20182.2
Super User
Posts: 3,944
Registered: ‎01-14-2017

Re: Comparing result sets at different levels of granularity

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

Community Support Team
Posts: 7,499
Registered: ‎08-14-2016

Re: Comparing result sets at different levels of granularity

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].

22.PNG

 

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])

21.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
Regular Visitor
Posts: 21
Registered: ‎05-03-2017

Re: Comparing result sets at different levels of granularity

Apologies for the delay.  Using this model has worked out.  Thank You,