Average of averages of averages DAX measure into a matrix visual

[MichiyoTora]

Hi everyone !

I have datas seems like this :

platform	domain	product	component	value
PDP	LEGACY MODERNIZATION PROGRAM	CARBON	intercompany-sales-pegase	29.016666666666666
PDP	LEGACY MODERNIZATION PROGRAM	CARBON	pegase-api-logistique	0.0048611111111111112
PDP	LEGACY MODERNIZATION PROGRAM	CARBON	pegase-api-logistique-v2	20.244444444444444
PDP	LEGACY MODERNIZATION PROGRAM	CARBON	perseus-costing-in-pegase	0.026388888888888889
PDP	LEGACY MODERNIZATION PROGRAM	CARBON	intercompany-price-pegase	20.245833333333334
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-spp-basa	67.2076388888889
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-pcmd-basa	60.880555555555553
PDP	LEGACY MODERNIZATION PROGRAM	BASA	api-spp-basa	54.071527777777774
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-api-spp-basa	8.2854166666666664
PDP	LEGACY MODERNIZATION PROGRAM	BASA	api-mn-basa	7.3041666666666663
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-pspsa-basa	39.205555555555556
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-pmn-int-basa	67.171527777777783
PDP	LEGACY MODERNIZATION PROGRAM	BASA	api-sps-basa	54.689583333333331
PDP	LEGACY MODERNIZATION PROGRAM	BASA	converter-mn-int-basa	59.181944444444447
PDP	OFFER BUILDING & OPTIMIZATION	PROA	proa-front	21.486805555555556
PDP	OFFER BUILDING & OPTIMIZATION	PROA	proa-optimizer	41.729861111111113
CCDP	SERVICES	AGENDE CONDIVISE	webappointment-backend-customer	0.92638888888888893
CCDP	SERVICES	AGENDE CONDIVISE	sharedagenda-backend	19.728472222222223
CCDP	SERVICES	AGENDE CONDIVISE	webappointment-frontend-customer	4.7763888888888886
CCDP	SERVICES	AGENDE CONDIVISE	legacy-adapter-for-webappointment	36.8625

We have to calculate the averages of the column "value", however we have to follow the rule : "all component have the same weight in a product, all product have the same weight in a domain, and all domain have the same weight in a platform. The goal is to have some table like this : 

So, I have to do average of the averages of products to have the Domain view. I tried a lot of things in dax but nothing work since product level ... (for example on the last screenshot, the value for the domain "LEGACY MODERNIZATION PROGRAM" should be CARBON + BASA / 2, i.e. 8.12, not 7.19)

I tried averagex with temp table but I don't know why this doesn't work.

measure = VAR comp = SUMMARIZE('table','table'[Platform],'table'[Domain],'table'[product_id],'K8S deployments'[component_name],"K1",CALCULATE(AVERAGEX('table',[Value])))
VAR prod = SUMMARIZE(comp,[Platform],[Domain],[product_id],"K1P",AVERAGEX(comp,[K1]))
VAR dom = SUMMARIZE(prod,[Platform],[Domain],"K1D",AVERAGEX(prod,[K1P]))
VAR plat = SUMMARIZE(dom,[Platform],"K1P",AVERAGEX(dom,[K1D]))
return AVERAGEX(plat,[K1P])

Can you help me please ?

[v-yueyunzh-msft · Accepted solution]

Hi , @MichiyoTora 

According to your description, you want to calculate the average for the same weight . This is my understand for your need :

If this , here are the steps you can refer to :
(1)This is my test data :

(2)We can create three measures like this:

Domain value = 
var _t = SUMMARIZE('Table' , 'Table'[domain],'Table'[product] ,"avg" , [Average Measure])
return
averagex(_t,[avg])

Platform Value = 
var _t = SUMMARIZE('Table' , 'Table'[platform],'Table'[domain] ,"avg" ,[Domain value])
return
averagex(_t,[avg])

Measure = IF( ISINSCOPE('Table'[component]) , [Average Measure] , IF( ISINSCOPE('Table'[product]) , [Average Measure] , IF(ISINSCOPE('Table'[domain] ) , [Domain value] ,
IF( ISINSCOPE('Table'[platform]) , [Platform Value]))))

(3)Then we can put the measures on the visual and we can get the result as follows and the [Measure] is the end result we need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi , @MichiyoTora 

According to your description, you want to calculate the average for the same weight . This is my understand for your need :

If this , here are the steps you can refer to :
(1)This is my test data :

(2)We can create three measures like this:

Domain value = 
var _t = SUMMARIZE('Table' , 'Table'[domain],'Table'[product] ,"avg" , [Average Measure])
return
averagex(_t,[avg])

Platform Value = 
var _t = SUMMARIZE('Table' , 'Table'[platform],'Table'[domain] ,"avg" ,[Domain value])
return
averagex(_t,[avg])

Measure = IF( ISINSCOPE('Table'[component]) , [Average Measure] , IF( ISINSCOPE('Table'[product]) , [Average Measure] , IF(ISINSCOPE('Table'[domain] ) , [Domain value] ,
IF( ISINSCOPE('Table'[platform]) , [Platform Value]))))

(3)Then we can put the measures on the visual and we can get the result as follows and the [Measure] is the end result we need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MichiyoTora]

Hi !
Thank's a lot, it works perfectly !!!
I doesn't know ISINSCOPE function, it's magic !

Last question, do you think we can have the grand total in the matrix visual ? 
Thx again !

[v-yueyunzh-msft · Accepted solution]

Hi , @MichiyoTora 

Sure , you can try to use this dax code :

Measure 2 = SUMX(VALUES('Table'[platform]) , [Measure])

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MichiyoTora]

You're awesome !
Thx again !

Best regards.