Forum Discussion

mizaskun's avatar
mizaskun
Icon for Helper II rankHelper II
5 years ago
Solved

How to custom average a measure

Good afternoon,

 

I am working with the following model:

 

Values

REGIONVALUE
EUROPE8
EUROPE3
EUROPE4
ASIA1
ASIA2
ASIA6
AMERICA5
AMERICA7

 

Weights

REGIONWEIGHT
AMERICA7
ASIA2
EUROPE14

 

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: 
REGIONVALUEWEIGHTAVRG
AMERICA1276
ASIA923
EUROPE15145

 

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:

REGIONVALUEWEIGHTAVRGC_WEIGHTTOTAL
AMERICA127678%4,67
ASIA92322%0,67
 sum9 result5,33

 

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

REGIONVALUEWEIGHTAVRGC_WEIGHTTOTAL
AMERICA127630%1,83
ASIA9239%0,26
EUROPE1514561%3,04
 sum23 result5,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,

DAX
Measures
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      mizaskun
      Icon for Helper II rankHelper II
      5 years ago

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