Forum Discussion

Brookied1974's avatar
Brookied1974
Frequent Visitor
3 years ago
Solved

Help request to define Subtotal in Column

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 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • Brookied1974's avatar
    Brookied1974
    Frequent Visitor
    3 years ago

    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.