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

[Gjakova]

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]

@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?

[mahoneypat · Accepted solution]

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

Regards,

Pat

[mahoneypat]

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

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

Regards,

Pat

[Gjakova]

@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):

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

[Greg_Deckler]

@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.