Forum Discussion
Get a Count from Measure
I have a table of employees and we keep track of attendance points. The points are accumulated from the last 6 months so I have a measure that sums those [Last6MonthPoints]. Based on that total, I used a measure to classify the total into Bins. So we have "good standing", "step 1", "step 2", and "step 3". The data is working as intended so far and is structured like what you see below.
Employee Name Last6MonthPoints Bin
Jim 8 Step 1
What I am wanting to do is get a count of the number of employees in each category: "good standing" = 5, "step 1" =3, etc....
I cannot do a calculated column for the point totals since it is multiple rows so I am running into a problem with getting the count. Maybe there is a way to summarize the total points in another table (one row per employee ) and jsut add a column for the Bins. If someone knows how to do this, that would be much appreciated.
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
I ended up changing my query quite a bit to get a total per employee per month. Then I added a calculated column to get the sum of points from the last 5 months (including this month). After getting this calc'ed column, I just added another column for the attendance standing i.e. good standing, step 1, etc....When I use this status column, I can now slice and filter by it.
Points 6 Month Total = calculate(sum(tblLeaveMaster[PointTotal]),
Allexcept( tblleavemaster, tblLeaveMaster[Employee Name]),
DATESBETWEEN(
tblLeaveMaster[Date],
Dateadd(lastdate(tblLeaveMaster[Date]), -4, Month),
//Four Month Ago
LastDate(tblLeaveMaster[Date])
)
)
3 Replies
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
I reworked the query to get a monthly summary per employee. What I need now is a calculated column to look at Month/Year and sum the last six months of the Points column. Anyone know how to do that?
Name Month/Year Points Team Name
Jim 7/2018 2 Team A
Mark 7/218 1 Team B
Bill 8/2018 4 Team C
Jim 8/2018 1 Team A
I ended up changing my query quite a bit to get a total per employee per month. Then I added a calculated column to get the sum of points from the last 5 months (including this month). After getting this calc'ed column, I just added another column for the attendance standing i.e. good standing, step 1, etc....When I use this status column, I can now slice and filter by it.
Points 6 Month Total = calculate(sum(tblLeaveMaster[PointTotal]),
Allexcept( tblleavemaster, tblLeaveMaster[Employee Name]),
DATESBETWEEN(
tblLeaveMaster[Date],
Dateadd(lastdate(tblLeaveMaster[Date]), -4, Month),
//Four Month Ago
LastDate(tblLeaveMaster[Date])
)
)