cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wilfredtsk Regular Visitor
Regular Visitor

Trying to get the percentage

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!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Datatouille Established Member
Established Member

Re: Trying to get the percentage

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

 

 

View solution in original post

9 REPLIES 9
Datatouille Established Member
Established Member

Re: Trying to get the percentage

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

 

 

View solution in original post

Omega Established Member
Established Member

Re: Trying to get the percentage

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 Regular Visitor
Regular Visitor

Re: Trying to get the percentage

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?

wilfredtsk Regular Visitor
Regular Visitor

Re: Trying to get the percentage

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?

Super User
Super User

Re: Trying to get the percentage

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.


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

Re: Trying to get the percentage

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

Super User
Super User

Re: Trying to get the percentage

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 %


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

Re: Trying to get the percentage

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

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

Super User
Super User

Re: Trying to get the percentage

Thank you.  It is in the ribbon.


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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,368)