How to custom average a measure

[mizaskun]

Good afternoon,

I am working with the following model:

Values

REGION	VALUE
EUROPE	8
EUROPE	3
EUROPE	4
ASIA	1
ASIA	2
ASIA	6
AMERICA	5
AMERICA	7

Weights

REGION	WEIGHT
AMERICA	7
ASIA	2
EUROPE	14

I need to subtotalize the values by region as a measure. Thus, after having inquired in the forum I got the following DAX expression:

AVRG = CALCULATE(

AVERAGE( 'VALUES'[VALUE] ),

VALUES( 'VALUES'[REGION] ),

ALLSELECTED( 'VALUES' )

)

Resulting on the following table: 

REGION	VALUE	WEIGHT	AVRG
AMERICA	12	7	6
ASIA	9	2	3
EUROPE	15	14	5

If I delete the Region column everything sums up in a line with a 4,50 average value. What I need is to customize that average calculation by using the Weights table values and the filtered data in the report.

If I select AMERICA and ASIA, the result should be:

REGION	VALUE	WEIGHT	AVRG	C_WEIGHT	TOTAL
AMERICA	12	7	6	78%	4,67
ASIA	9	2	3	22%	0,67
	sum	9		result	5,33

If I select AMERICA, ASIA and EUROPE, the result should be:

REGION	VALUE	WEIGHT	AVRG	C_WEIGHT	TOTAL
AMERICA	12	7	6	30%	1,83
ASIA	9	2	3	9%	0,26
EUROPE	15	14	5	61%	3,04
	sum	23		result	5,13

This result should remain even if I delete columns from the table. If I just want to show the TOTAL column, the result value should be the same (5,33 and 5,13 depending on the report filters).

How could I achieve this? 

Thank you so much in advance,

[Jihwan_Kim · Accepted solution]

Hi, @mizaskun 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

https://www.dropbox.com/s/w38enf8sb5ysogt/mizaskun.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mizaskun]

Thank you so much @Jihwan_Kim !! This works fine! I will work on transferring the formula to my model.