Quick way to get an average of multiple averages?

[RobinA_Bi]

Hi Community,

I'm making a data dashboard that shows a total score of four different averages. I have two problems. 

So my Idea how to solve this (this is probably a very inefficient way) was to make four indivudal measures that calculates the average score. Then, when I have those average score, i simply (a+b+c+d )/ 4 to get the average score of all the other scores.(named total score in the dashboard) 

First problem: when I make a quick measure and use "average by category" I get the wrong average score. Don't know why.

second problem: when I make the fifth measure for the total score that should be the average of all, this number also is wrong because the other average score is wrong.

third problem: even if I get this to work, it was really time consuming to create so many measures. Is there a better way? Below are the steps how I went about this...

1. Created a quick measure for each

average - general impression
average - learning experience

average - useful employee

average - useuful company name

2. Made a fifth measure

total score average = ([average - general impression] + [average - learning experience] + [average - useful employee] + [average - useuful company name])  / 4


Any idea what I should do?

Best regards,

Robin

[v-rongtiep-msft]

Hi @RobinA_Bi ,

Please have a try.

You can write the measures by yourself.

average - general impression=
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[name] = SELECTEDVALUE ( 'Table'[name] ) ),
    'Table'[General Impression]
)

average - learning experience=
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[name] = SELECTEDVALUE ( 'Table'[name] ) ),
    'Table'[learning experience]
)

average - useful employee=
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[name] = SELECTEDVALUE ( 'Table'[name] ) ),
    'Table'[useful employee]
)

average - useuful company name=
AVERAGEX (
    FILTER ( ALL ( 'Table' ), 'Table'[name] = SELECTEDVALUE ( 'Table'[name] ) ),
    'Table'[useuful company name]
)

Then the total average.

total score average = ([average - general impression] + [average - learning experience] + [average - useful employee] + [average - useuful company name])/ 4

If I have misunderstood you meaning, please provide your pbix file without privacy information and desired output with more details.

How to Get Your Question Answered Quickly 

Use quick measures for common calculations 

Best Regards

Community Support Team _ Polly

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

[RobinA_Bi]

Hi again!

Finally back at work and trying this out now.

I'm trying to write the DAX example that you have, but it's something wrong with the syntax for me. Would you be so kind of help me explain what I can replace your placeholder text with? This is a screen shot:

1. I created a new measure.
2. The datafield I want to have an average of is called: GE - Data - General Impression

Best regards,

Robin

[v-rongtiep-msft]

Hi @RobinA_Bi ,

You are using two equal signs in the same formula, and it is not used to define the formula either. So if you want to change the name you can just replace the previous one.

It seems that your formula is not complete.

If I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[RobinA_Bi]

Thank you for the help.

Sorry, I fixed that, but I still get error:

Is there any particular reason why I should to use averagex? Because I believe that average value is fairly easy to get, but my biggest challenge was to get the "total score" which is the average of the other four averages: general impression, learning experience, useful employee, useful company.

Thanks again for the help!

[v-rongtiep-msft]

Hi @RobinA_Bi ,

Could you please provide a simple sample? If you data are privacy, could you provide some similar dummy data and the desired output?

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

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

[RobinA_Bi]

Hi!

I made this simple example in Excel (because I don't know how to in BI)

I didn't find a way to share the excel file itself here.

There you have an average for each of the four statistics for the measure. Finally, a fifth measure that makes an average of all these. Do you know the best and most efficient way to do this in BI?

Best regards,
Robin

[v-rongtiep-msft · Accepted solution]

Hi @RobinA_Bi ,

Please refer to my pbix file to see if it helps you.

Create 5 measures.

course usefule for the company =
VAR _result =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[is the course usefule for the company] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Column1] ),
        MAX ( 'Table'[anonymous- General impression] ),
        _result
    )

course usefule for the company =
VAR _result =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[is the course usefule for the company] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Column1] ),
        MAX ( 'Table'[anonymous- General impression] ),
        _result
    )

general impression =
VAR _result =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[anonymous- General impression] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Column1] ),
        MAX ( 'Table'[anonymous- General impression] ),
        _result
    )

Learning experience_measure =
VAR _result =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[Learning experience] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Column1] ),
        MAX ( 'Table'[anonymous- General impression] ),
        _result
    )

all_average =
VAR _nowrow = [course useful to you] + [course usefule for the company] + [general impression] + [Learning experience_measure]
RETURN
    _nowrow / 4

If I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[RobinA_Bi]

Thank you so much!!! 🙂

Very kind of you.