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 column subtotal based on attendance code "A" only .

Foe example, If date columns have three "A's" then column subtotal will display count of A. ie. 3 other wise 0.  

Image

  • 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

     

18 Replies

  • lukiz84's avatar
    lukiz84
    Memorable Member
    3 years ago

    What measure do you use in the values section?

  • sd_parekh's avatar
    sd_parekh
    Helper I
    3 years ago

    In value section I used column value. Its stored in table.

    I wrote "CountL2W days" dax function however power bi did not allow me to drop that measure into row section.

    If I drop that function into the value section it creates group with attendance code.

    Refer attached image. 

    Also If I use same measure in a matrix of value section without attendance code field it shows me the correct result but I need first matrix which will have last column named  "CountL2W days" same as second matrix. 

  • lukiz84's avatar
    lukiz84
    Memorable Member
    3 years ago

    So is "First AttendenceCode" a measure? If so, please share the code

    • sd_parekh's avatar
      sd_parekh
      Helper I
      3 years ago

      Hi, First attendance code is not a measure its column value coming from table. 

      Attendance code have  "A", "P", "NRA" values. 

  • lukiz84's avatar
    lukiz84
    Memorable Member
    3 years ago

    Ok, then you have to create a measure:

     

    AttendeceCode Measure =
   IF(
      HASONEVALUE(table['AttendenceDate']),
      MIN(table['First AttendenceCode']),
      COUNTROWS(
         FILTER(
             table,
             table['First AttendenceCode'] = 'A'
         )
      )
    )

     

    and use this instead of just putting "Attendance code" in your values section

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