Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sd_parekh
Helper I
Helper I

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.  

ImageImage

2 ACCEPTED SOLUTIONS

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

 

View solution in original post

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

 

View solution in original post

18 REPLIES 18
lukiz84
Memorable Member
Memorable Member

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

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. 
sd_parekh_1-1665714612920.png

 

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

 

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)

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.
sd_parekh_1-1665716851557.png

 


Thanks. 

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

 

 

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

 

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. 

Yes, the IF is necessary here, otherwise it would always return at least 0 - but you just want to show zero if there are ANY details 🙂 Glad i could help.

Thanks again. 😀 . Can I ask you one more question? 

sure 🙂

Previously in a matrix I used AttendanceCode and I used it for conditional formating for background colour.

Now we are using Measure and measure will return value in number. 

 

So how can we do conditional formatting using our defined measure?

Refer image

sd_parekh_0-1665721756054.png

 

 

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

 

Hi,

Thanks for your help one more time.

Is there any way I can contact you directly if I have any question, of course with power bi community. 

sure just send me a private message anytime 🙂

lukiz84
Memorable Member
Memorable Member

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

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

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

sd_parekh
Helper I
Helper I

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

I wrote "CountL2W days"tempsnip2.png 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
Memorable Member
Memorable Member

What measure do you use in the values section?

Please read my second comment/Post. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.