cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

How to count the amount of numbers in a scatterchart (conditional formatting with colors)

Hi there!

I have a question which I can't seem to solve...

Someone from my team asked if it is possible to do a gradient color based on the amount of times a certain grade has been given.

I thought of doing a conditional formatting based on COUNT(grade), but it of course counts all the grades. So I basically would like to COUNT the amount a certain grade (1 till 10) was given and do a gradient on that.

I know that the average grade in this dataset is around 7, so I would expect that the line around 7 is colored more intensively/darker compared to grades around 2 and 9.

My measures are as follows:

- Y Axis: Sum of Grade (1 till 10, could also be grades with 2 decimals, but I put an INT to round the grades)
- Details: RowID (Based on my fact table where all the grades per student, per period, per exam etc. are stored)
- X Axis: Sum of Range (1 till 64, which represent the year, period and exam number, since the school does not work with dates)

Hope someone could help me or lead me to the right direction! Thanks in advance 🙂

Gjakova_0-1598088757104.png

 

1 ACCEPTED SOLUTION

Try changing ALL(Grades) to ALLSELECTED(Grades) in the measure, and let me know if that doesn't fix it.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
Helper V
Helper V

@mahoneypat I think that the problem might be that it counts all the grades instead of how many times a 1 has been given, and a 2 and a 3 ...... and a 9 etc. 
I think that would solve the problem maybe?

Try changing ALL(Grades) to ALLSELECTED(Grades) in the measure, and let me know if that doesn't fix it.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Super User IV
Super User IV

You could also consider using the size field to differentiate the count at each grade/exame (it would be easier).  I made a small dataset like yours and used this measure to count the number or RowIDs that had the same grade for the same exam.  You could adapt it for your table/column names.

 

Count Same Grade = CALCULATE(COUNT(Grades[RowID]), ALL(Grades), VALUES(Grades[Grade]), VALUES(Grades[Period]))
 
 

Use the measure in the format options for your visual.  Hit fx next to Data Color

color2.png

 

color1.png

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat Thanks a lot! This measure works indeed. You saved me a lot of headache and time 🙂 thank you for that!

@mahoneypat update:

I was checking how it works If I select 1 student for 1 course during 1 period so in theory all the dots should be the same color, because that certain grade has only been given once.

But when I did that, it still showed a gradient view of the grades (see picture below):

Gjakova_0-1598193464244.png

Would that measure be thus incorrect? Hope you can still help me out @mahoneypat 

Super User IV
Super User IV

@Gjakova Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors