Create an Attainment Metric using fact tables at distinct granularity level

[arlequin71]

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:

This is the Quota Table

This is the Quota Table

Country	BG	DIV	CustomerCD	Quota
COLOMBIA	AAA	A	A44586	200
COLOMBIA	AAA	B	A44586	100
COLOMBIA	AAA	C	A44586	50
COLOMBIA	AAA	D	A44586	40
COLOMBIA	AAA	E	A44586	130
COLOMBIA	AAA	F	A44586	220
ECUADOR	AAA	A	E2022799	200
ECUADOR	AAA	B	E2022800	200
ECUADOR	AAA	D	E2022801	300
ECUADOR	AAA	E	E2022802	120
ECUADOR	AAA	F	E2022803	150
BOLIVIA	BBB	G	BO98R4505	200
BOLIVIA	BBB	H	BO98R4506	140
BOLIVIA	BBB	I	BO98R4507	130

And this is the Sales Table

COUNTRY	BG	CustomerCD	Sales
COLOMBIA	AAA	A44586	700
ECUADOR	AAA	EC2022799	900
BOLIVIA	BBB	BO98R4505	400

[arlequin71]

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

[yingyinr · Accepted solution]

Hi @arlequin71 ,

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

Best Regards