Forum Discussion

sd_parekh's avatar
sd_parekh
Helper I
3 years ago
Solved

Matrix column Subtotal

Hi all, My question is related to matrix visual. Row headers : Student name ,academic year and class name Column header: Date  Values: Attendance Code Refer attached image.  I want calculate co...
  • lukiz84's avatar
    lukiz84
    3 years ago

    Hi,

     

    looks good! Just add the zero like below:

     

     

    attendance code measure =
IF(
      HASONEVALUE(AttendanceDetails[AttendanceDate]),
      MIN(AttendanceStatus[AttendanceCode]),
      COUNTROWS(
         FILTER(
             AttendanceDetails,
             AttendanceDetails[AttendanceStatusId] = "2938951904584020836"
             )
      ) + IF(COUNTROWS(AttendanceDetails) > 0, 0)
  )

     

  • lukiz84's avatar
    lukiz84
    3 years ago

    for conditional formatting you need another measure now, sorry.

     

    just create a measure with

     

    condFormatMeasure =

SWITCH(TRUE(),

   [attendence code measure] = "A", 1,

   [attendence code measure] = "P", 2,

   [attendence code measure] = "PA", 3,

   -1

)

    and use this one for the rules in conditional formatting

     

sd_parekh's avatar
sd_parekh
Helper I
3 years ago

Thanks. 

I wrote following measure. 

attendance code measure =
IF(
      HASONEVALUE(AttendanceDetails[AttendanceDate]),
      MIN(AttendanceStatus[AttendanceCode]),
      COUNTROWS(
         FILTER(
             AttendanceStatus,
             AttendanceStatus[AttendanceCode] = "A"
         )
      )
    )
It gives me following output. 

 

In column subtotal I want count of A.  Same as  second matrix last column just included for your reference.

 

lukiz84's avatar
lukiz84
Memorable Member
3 years ago

AttendanceStatus is just the dim table right? So each of the codes is only listed once? 

 

You need to count the rows from AttendanceDetails, not from AttendanceStatus (Because thats always 1)

  • sd_parekh's avatar
    sd_parekh
    Helper I
    3 years ago

    hi, Correct.

    I used below code.

    attendance code measure =
    IF(
          HASONEVALUE(AttendanceDetails[AttendanceDate]),
          MIN(AttendanceStatus[AttendanceCode]),
          COUNTROWS(
             FILTER(
                 AttendanceDetails,
                 AttendanceDetails[AttendanceStatusId] = "2938951904584020836"
                 )
             )
          )
    Got following result.

     


    Thanks. 

    But how can we add count 0 in rest of the values in Total Column (Column subtotal)?

     

     
    • lukiz84's avatar
      lukiz84
      Memorable Member
      3 years ago

      Hi,

       

      looks good! Just add the zero like below:

       

       

      attendance code measure =
IF(
      HASONEVALUE(AttendanceDetails[AttendanceDate]),
      MIN(AttendanceStatus[AttendanceCode]),
      COUNTROWS(
         FILTER(
             AttendanceDetails,
             AttendanceDetails[AttendanceStatusId] = "2938951904584020836"
             )
      ) + IF(COUNTROWS(AttendanceDetails) > 0, 0)
  )

       

      • sd_parekh's avatar
        sd_parekh
        Helper I
        3 years ago

        I tried to put +0 outside the if statement but it broke my visual. Then after you suggestion I put it inside and it works fine as required. Thanks for you help for solving my query.