cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arlequin71
Helper I
Helper I

Create an Attainment Metric using fact tables at distinct granularity level

Hello,

I require to create an attainment metric for sales vs quota applied to the attached simplified model.

The Quota table granularity is at Division Level (nested child) and Sales table has BG level (parent).  I would prefer to approach this using a DAX. I tried to replicate the solution from Budget – DAX Patterns but i haven't had luck yet.

 

The desired result is a table like the attached one, may be using Isinscope to hide values at division level.

 

Could someone give me some tip to solve it ?. Thanks so much in advance.

 

This is the desired result:

arlequin71_0-1621455318862.png

 

This is the Quota Table

 

arlequin71_1-1621454597030.png

 

This is the Quota Table

CountryBGDIVCustomerCDQuota
COLOMBIAAAAAA44586200
COLOMBIAAAABA44586100
COLOMBIAAAACA4458650
COLOMBIAAAADA4458640
COLOMBIAAAAEA44586130
COLOMBIAAAAFA44586220
ECUADORAAAAE2022799200
ECUADORAAABE2022800200
ECUADORAAADE2022801300
ECUADORAAAEE2022802120
ECUADORAAAFE2022803150
BOLIVIABBBGBO98R4505200
BOLIVIABBBHBO98R4506140
BOLIVIABBBIBO98R4507130

 

And this is the Sales Table

 

COUNTRYBGCustomerCDSales
COLOMBIAAAAA44586700
ECUADORAAAEC2022799900
BOLIVIABBBBO98R4505400

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @arlequin71 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

yingyinr_0-1621830573673.png

Best Regards

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

View solution in original post

2 REPLIES 2
arlequin71
Helper I
Helper I

Excellent, thank you very much.  This is exactly what i requred.

 

 

yingyinr
Community Support
Community Support

Hi @arlequin71 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

yingyinr_0-1621830573673.png

Best Regards

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors