Producing a Pie Chart From a Calculated Measure

[IanGladden]

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. 

[Icey]

Hi @IanGladden ,

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

Best Regards,

Icey

[IanGladden · Accepted solution]

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]

Hi @IanGladden ,

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

Best Regards,

Icey

[IanGladden]

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.