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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshcomputer1
Helper V
Helper V

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. 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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