Forum Discussion

IanGladden's avatar
IanGladden
Frequent Visitor
6 years ago
Solved

Producing a Pie Chart From a Calculated Measure

You will have to excuse me if some of the terminology is not correct but I'm not a programmer by any stretch and I'm trying to teach myself this package so I can make our school data more accessible and transparent.

 

So after my excuses are done I'll explain my issue.

 

I have a number of reports running from our School MIS and I want to be able to show all students who have been persistently absent, which is defined as less than 90% attendance, this academic year. I have a table that shows in columns the student ID, their attendance code for every day, so that is a row for every day in this academic year, and the statistical meaning for that code. I then have calculated measures for number of authorised absences, number of present and number of unauthorised absences.

 

n_auth_abs = COUNTAX(FILTER(attendance,attendance[Statistical meaning]="Authorised Absence"),Attendance[External Id])
n_present = COUNTAX(FILTER(attendance,attendance[Statistical meaning]="Present"),attendance[External Id])+COUNTAX(FILTER(attendance,attendance[Statistical meaning]="Approved Educational Activity"),attendance[External Id])
n_unauth_abs = COUNTAX(FILTER(attendance,attendance[Statistical meaning]="Unauthorised Absence"),attendance[External Id])
%att = [n_present]/([n_auth_abs]+[n_unauth_abs]+[n_present])
 
I then calculate their % attendance with another calculated measure
 
%att = [n_present]/([n_auth_abs]+[n_unauth_abs]+[n_present])
 
The I calculate the student PA with;
 
PA = if(Attendance[%att]<0.9,"Y","N")
 
It's now that I'm struggling because I want to just work out the % of students who are now PA to put in a simple pie chart as a cohort.
 
I did a an if statement as a measure that if PA was Y then 1, if not a 0 and then tried a pie chart with that measure as the value and as a percent of grand total but it shows nothing.
 
Any assistance would be gratefully received.
 
Any guidance would be most appreciated. 
 
Need Help
  • IanGladden's avatar
    IanGladden
    6 years ago

    Thanks for the offer but I managed to produce what I wanted with a measure.

     

    PA = SUMX(VALUES(Attendance[External Id]),IF(AND(ISNUMBER(Attendance[%att]),Attendance[%att]<=.9),1,0))/SUMX(VALUES(Attendance[External Id]),IF(ISNUMBER(Attendance[%att]),1,0))

     

    Where External ID is the Unique ID, %att is a measure that calculates their % attendance.

4 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi IanGladden ,

    If you don't mind, please share me some sample data or your PBIX file without sensitive information.

     

    Best Regards,

    Icey

    • IanGladden's avatar
      IanGladden
      Frequent Visitor
      6 years ago

      Thanks for your offer, apologies for the late reply but the email notification went into my junk folder.

      I'll produce a sanitised data PBIX file this weekend and send it on to you then.

    • IanGladden's avatar
      IanGladden
      Frequent Visitor
      6 years ago

      Thanks for the offer but I managed to produce what I wanted with a measure.

       

      PA = SUMX(VALUES(Attendance[External Id]),IF(AND(ISNUMBER(Attendance[%att]),Attendance[%att]<=.9),1,0))/SUMX(VALUES(Attendance[External Id]),IF(ISNUMBER(Attendance[%att]),1,0))

       

      Where External ID is the Unique ID, %att is a measure that calculates their % attendance.

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi IanGladden ,

        Glad to hear that. Please accept your reply above as a solution.

         

        Best Regards,

        Icey