Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What i want is to create a visual that shows the count of people who are over or under the requirement (60 min for each month, so 360 min in june). I want the users to be able to select the count for "over requirements" and then see only the people over the requirement for the month in the matix. Anyone with a good advice?
So this is my matrix, and my tables:
And here are the measures I've used in the matrix:
TotalYTD = TOTALYTD(SUM(Trainings[TrainingHour]);'Date'[Date])
Hi @Anonymous,
nice question. To do it even better next time, add some sample data by text or link to a file in dropbox(or similar). When you post it as a picture, it means someone who wants to help you has to manually punch the numbers:
How-to-Get-Your-Question-Answered-Quickly
Now, create at new table, with 1 column and 2 rows, with "Under" and "Over" on the rows. I have called the table Requirements and the column Requirement. This table should not have relationnships to any other tables. In your Traning-table, create theese measures:
TotalYTD_noFilter = TOTALYTD(SUM(Training[TrainingHour]);dimDate[Date])
TotalYTD_over = VAR ytd_requirement = [TotalYTD_noFilter] - 60 * ( SELECTEDVALUE ( dimDate[Month number] ) - 1 ) RETURN IF ( ytd_requirement >= 60; [TotalYTD_noFilter]; BLANK () )
TotalYTD_under = VAR ytd_requirement = [TotalYTD_noFilter] - 60 * ( SELECTEDVALUE ( dimDate[Month number] ) - 1 ) RETURN IF ( ytd_requirement < 60; [TotalYTD_noFilter]; BLANK () )
TotalYTD = IF ( SELECTEDVALUE ( Requirement[R] ) = "Over"; [TotalYTD_over]; IF ( SELECTEDVALUE ( Requirement[R] ) = "Under"; [TotalYTD_under]; [TotalYTD_noFilter] ) )
Use TotalYTD in your matrix, change the conditional formatting to Rules and add rules for FillColour=1 (as number) and FillColour=2 (as number).
Add a slicer with Requirements[Requirement].
Thank you so mutch @sturlaws ! Next time I will publish the dataset as well 🙂
But is there anyway it will be possible to count how many employees thats over the limit in a spesific month? eg. count only the people that are over 360 minutes in june
Having a bit of trouble with that. I can create a calculated table like this(in the modelling tab of power bi)
Table = GENERATEALL ( VALUES ( dimDate[Month number] ); ADDCOLUMNS ( VALUES ( Training[Name] ); "TotalYTD_over"; [TotalYTD_over] ) )
and it correctly identifies the number persons over required level of training pr month. But when I try to use that piece of code inside a measure, I can't get the right count for july.
If it is acceptable to have a table as generated by the code above, it is easy to create a measure counting the number of persons. If not, I will look into it further, but will be a couple of days before I will have the time to look into it again.
Thank you so much @sturlaws !
Out of sheer curiosity and that I can probably imagine doing this at a later date also I really want to know how this can be solved - if you have time 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |