Trying to get the percentage

[wilfredtsk]

Hi guys, I have a entry question but this has been bugging me for the entire day.

I am trying to get a graph showing the % of my students who scored 80marks and above (of a particular subject)

what I have:

~CSV file of 800 students' score detailing the following

• Student A, Math, 60
• Student A, English 70
• Student B, Math, 90
• Student B, English, 60
• Student C, Math, 65
• Student C, English 90

I used the scores as Values and subjects as Axis

I know what I want... but can't seem to visualise properly. Any expert help pls? tks!!!!

[Omega]

Try creating 2 measures:

Score < 80 = COUNTROWS(FILTER(Table1, [Score] <= 80))

Score > 80 = COUNTROWS(FILTER(Table1,[Score]>80))

Then use the 100% stacked column chart and drag both as values and Subject as Axis 😄

[wilfredtsk]

Hey khader312,

I tried your method and got the same issue too... "DAX comparison operations do not support comparing values of type text with values of type integer"...

My data is in .csv... (i.e. no formatting, etc) so, I'm quite perplexed to why this is happening... Is it because that there could be "blank" in some of the marks?

[Datatouille · Accepted solution]

Hi @wilfredtsk

Welcome to the Power BI & DAX World !

You need to create the following measures ("Modelling Tab" > " New Measure")

NbMarks = Countrows(YourTable)

NbMarksSupto80 = Calculate ( [NbMarks] , YourTable[MarkColumn] > 80 )

PctAbove80 = Divide ( [NbMarksSupto80]  , [NbMarks]  )

Format your [PctAbove80] measure as % and use it as "Values".

[wilfredtsk]

Hey Excelside, I have got an error after trying the method...

"MdxScript (Model) (4, 63) Calculation error in measure 'SA1 2017_Primary'[NbMarksSupto50]: DAX comparison operations do not support comparing values of type test with values of type integer. Consider using the VALUE or FORMAT function to convert one of the values"

What do you think went wrong?

[Ashish_Mathur]

Hi @wilfredtsk,

This error tells us that the data type of the marks column is text.  In Power Query, change the data type of the Marks column to Numbers.

[wilfredtsk]

Tks for the quick reply. It works!!!

Last question, how do I set the "measure" to display as percentage instead? Now it is displaying as 0.2 to 0.8 (at the y-axis)....

I want it to display as 20% to 80%.....

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.  Just click on the measue in the right hand side pane and selecting the formatting as %

[wilfredtsk]

yes, it helped (and gave it a thumbs up!).

for the percentage, is it under the <visualizations> pane or under <fields> pane?

[Ashish_Mathur]

Thank you.  It is in the ribbon.