Forum Discussion

lukeSDM's avatar
lukeSDM
Icon for Helper V rankHelper V
6 years ago
Solved

wrong calculation and not showing in graph correctly

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

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

9 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi  lukeSDM 

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

         

        Regards,

        Lin

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • lukeSDM's avatar
      lukeSDM
      Icon for Helper V rankHelper V
      6 years ago

      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
       
      AdnoMarkMark dateAM/PMStatistical meaning
      384742L07 January 2020AMPresent
      384742\07 January 2020PMPresent
      384742U08 January 2020AMUnauthorised Absence
      384742\08 January 2020PMPresent
      384742L09 January 2020AMPresent
      384742\09 January 2020PMPresent
      384742/10 January 2020AMPresent
      384742\10 January 2020PMPresent
      384742/13 January 2020AMPresent
      384742\13 January 2020PMPresent
      384742/14 January 2020AMPresent
      384742\14 January 2020PMPresent
      384742/15 January 2020AMPresent
      384742\15 January 2020PMPresent
      384742L16 January 2020AMPresent
      384742\16 January 2020PMPresent
      384742/17 January 2020AMPresent
      384742\17 January 2020PMPresent
      384742/20 January 2020AMPresent
      384742L20 January 2020PMPresent
      384742/21 January 2020AMPresent
      384742\21 January 2020PMPresent
      384742/22 January 2020AMPresent
      384742\22 January 2020PMPresent
      384742/23 January 2020AMPresent
      384742\23 January 2020PMPresent
      384742L24 January 2020AMPresent
      384742\24 January 2020PMPresent
      384742/27 January 2020AMPresent
      384742\27 January 2020PMPresent
       
       
  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

     

      • negi007's avatar
        negi007
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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])))