cancel
Showing results for
Did you mean:
Highlighted
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
Established Member

## Re: Trying to get the percentage

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

9 REPLIES 9
Established Member

## Re: Trying to get the percentage

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

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 😄

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?

Regular Visitor

## Re: Trying to get the percentage

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

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

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

## Re: Trying to get the percentage

Thank you.  It is in the ribbon.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

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

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

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,092)