The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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.
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
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
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!
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
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
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
Thank you so much!!! 🙂
Very kind of you.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |