wrong calculation and not showing in graph correctly

[lukeSDM]

@AllisonKennedy  maybe you will know the answer

I am trying to work out the attendance percentage for each student over time.

Each Adno number (student) gets 2 attendance marks for each date for the AM and the PM the marks are as follows ; / - present in the AM, \ - present in the PM, I - ill/authorised absence, O - unauthorised absence ETC.

I have a calculation that calculates the two present marks (/ &\) and then I divide this by the count of all rows.

However when I do this it only gives me a result of 50% or 100% ie if they are present for the whole day and if they get 1 present mark they get 50% but when they are not present that day I would like it to go to zero% 

i have fixed my graph so that it shows all days but when the % should be zero it just skips across so i have an inconplete line!

Any help would be really appreciated and I have attached some images to provide some context.here you can see a specific student was not present at all that day but the line stops instead of going to zero

[v-lili6-msft · Accepted solution]

hi  @lukeSDM 

Add a date table by this formula:

Date = CALENDAR(MIN('Attendance overtime'[Mark date]),MAX('Attendance overtime'[Mark date]))

Then create a relationship with [Mark date]

and adjust the measure as below:

/\ % = [count of /\] / COUNTA('Attendance overtime'[Mark]) *100 + 0

Now use date field from date table in the x-axis in line visual.

Result:

here is sample pbix file, please try it.

Regards,

Lin

[lukeSDM]

Thank you this has resolved my issue!

[v-lili6-msft]

hi  @lukeSDM 

It's pleasant that your problem has been solved, 😁 could you please mark the reply as Answered?

Regards,

Lin

[negi007]

@lukeSDM  I think your Y axis is starting from 50%. Please change it to start from 0 (Zero), your chart will apear as you want.

[lukeSDM]

Unfortunately this was not the case!

[negi007]

@lukeSDM  Can you try using below DAX in your measure

Count = (CALCULATE(COUNTROWS('Marks_tab'),'Marks_tab'[Mark] in {"/","\"}))/(CALCULATE(COUNTROWS('Marks_tab'),ALL('Marks_tab'[Mark])))

[lukeSDM]

@negi007 

Unfortunately this still does not resolve my issue, as it does not go to 0% when the student has no present marks for that day.

I need it so when they are presant all day (2present marks out of 2, 1 for am and one for pm) it is 100% for that day, then when they are presant half the day ( 1 presant mark out of 2 ) it equals 50% and then when they are not present for that day (0 presant marks out of 2) it is 0%. 

That is essentially what i need.

I might need to use the statistical meaning instead of the mark as well due to 'L' also being present but technically they are late. so it might be worth counting the statistical meaning instead of the mark itself.

[amitchandak]

@lukeSDM ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lukeSDM]

Please find the spreadsheet my data comes from.

my measure for counting the present mark is : 

count of /\ = CALCULATE(COUNTROWS('Attendance overtime'),'Attendance overtime'[Mark] IN {"/", "\"})

and i work the % out by doing:

/\ % = [count of /\] / COUNTA('Attendance overtime'[Mark]) *100

Adno	Mark	Mark date	AM/PM	Statistical meaning
384742	L	07 January 2020	AM	Present
384742	\	07 January 2020	PM	Present
384742	U	08 January 2020	AM	Unauthorised Absence
384742	\	08 January 2020	PM	Present
384742	L	09 January 2020	AM	Present
384742	\	09 January 2020	PM	Present
384742	/	10 January 2020	AM	Present
384742	\	10 January 2020	PM	Present
384742	/	13 January 2020	AM	Present
384742	\	13 January 2020	PM	Present
384742	/	14 January 2020	AM	Present
384742	\	14 January 2020	PM	Present
384742	/	15 January 2020	AM	Present
384742	\	15 January 2020	PM	Present
384742	L	16 January 2020	AM	Present
384742	\	16 January 2020	PM	Present
384742	/	17 January 2020	AM	Present
384742	\	17 January 2020	PM	Present
384742	/	20 January 2020	AM	Present
384742	L	20 January 2020	PM	Present
384742	/	21 January 2020	AM	Present
384742	\	21 January 2020	PM	Present
384742	/	22 January 2020	AM	Present
384742	\	22 January 2020	PM	Present
384742	/	23 January 2020	AM	Present
384742	\	23 January 2020	PM	Present
384742	L	24 January 2020	AM	Present
384742	\	24 January 2020	PM	Present
384742	/	27 January 2020	AM	Present
384742	\	27 January 2020	PM	Present