Help request to define Subtotal in Column

[Brookied1974]

Hi All, 

Hope you are all well,  My Friday brain has kicked in and cannot figure this one out.  So i am building a office occupancy report and was asked if i could add a staff frequency visual.  i have the below which when i use dept filters and weekly filters will show how many times that person was in this week.  I have a data set where i count if a user had a flag activiated on a keypass panel for exit of said building, if so then they were in that day.  I am using distinctcount to show 1 entry per day recorded per person as one person could exit 27 times but i just need one instance.  I can get a total count (how many per day) as per below but i want a count per column on the right and if the count is 0 or 1 in any given week i can highlight and ask the "why" .  i want the count to reflect for example from below

Row 1 = 2

Row 2 = 4

Row 3 = 1

When i turn on column subtotals and row subtotals I get a value of 1 on everything so i used the below dax but this gave me the total for rows not columns 

Event Count =

SUMX(DISTINCT('Attendance MD'[Event]),

CALCULATE(DISTINCTCOUNT('Attendance MD'[Full Name])))

I have a table called "Attendance MD" 

fields i am using are:- Full name, Event (distinct Count)

 Thanks in advance and Happy new year 

[Brookied1974]

HI, thanks a million, that worked although i did make a slight change to my dataset as i had multiple events on the same day so i did some clean up to help the measure.   Thanks though. 

[Anonymous · Accepted solution]

Hi  @Brookied1974 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table1=
SUMMARIZE('Attendance MD','Attendance MD'[Event],"Value",[Event Count])
var _if=
IF(
    ISINSCOPE('Attendance MD'[Event]),[Event Count],SUMX(_table1,[Value]))
var _table2=
SUMMARIZE('Attendance MD','Attendance MD'[Full name],"Value",[Event Count])
return
IF(
   ISINSCOPE('Attendance MD'[Full name]),_if,SUMX(_table2,[Value]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly