Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wilfredtsk
Helper I
Helper I

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

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
Omega
Impactful Individual
Impactful Individual

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 😄

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?

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

 

 

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?

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/

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

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/

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

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

Thank you.  It is in the ribbon.


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.