cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RossBruna12
New Member

How to calculate an amount and average using several input rows

Hello! 

I'm new to the whole Power Bi scene and I was wondering if somebody could maybe help me.

I have the following kind of data:

 

GenderAgeScore
Male18-2510
Female35-455
Female50+0
Male26-345
Female50+0
Male18-2510

And is was wondering two things:
1. Is it possible (and how) to get a visualisation that shows for each age group what the amount of people are that scored a certain score?
2. Is it possbile (and how) to get a visualisation that shows for each age group what the average score of that age group is?
Does anybody maybe know how to do this?

 

Greetings!

6 REPLIES 6
v-polly-msft
Community Support
Community Support

Hi @RossBruna12 ,

Did my answer solve your problem? Or maybe you've already solved the problem. If so, please share your process with us.

 

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.

v-polly-msft
Community Support
Community Support

Hi @RossBruna12 ,

Please have a try.

Create a table to delete repeat value.

Table 2 = DISTINCT('Table')

Create a measure to calculate the average.

Measure = CALCULATE(AVERAGE('Table 2'[Score]),FILTER(ALL('Table 2'),'Table 2'[Age]=SELECTEDVALUE('Table 2'[Age])&&'Table 2'[Gender]=SELECTEDVALUE('Table 2'[Gender])))

11.PNG

If I  misunderstood your meaning, please provide more details and desired output.

 

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.

 

Ashish_Mathur
Super User
Super User

Hi,

What end result are you expecting?  Also, why are there duplicate entries for Male 18-25 and Female 50+


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Im expecting to get some type of graph showing that there are (for example) two people in the age group 18-25 who scored a 10.

And another graph showing that the average score of that age group is a 10.

 

The reason that there are dublicate entries is that this data comes from a questionnaire. 

 

Greetings! 

Hi,

Create a matrix visual.  Drag Gender and Score to rows/columns and write this measure

Respondents = countrows(Data)

In another visual, drag Age to the table/matrix visual and write this measure

Average age = average(Data[age])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

 

I don't quite understand what the measures are supposed to do and where they are supposed to go since Im pretty new to Power Bi.
Could you maybe eleborate on this?

 

Greetings!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!