Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mleepin
Helper I
Helper I

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,

 

 

 

 

2 ACCEPTED SOLUTIONS

Hi,

 

You may download my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

 

You may download my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.