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.
Hi!
I have a table with some staff training information:
training_table
Staff Name | Technology | Sub-Technology | Training Level |
Richard | Software | Software A | 1 |
Richard | Software | Software B | 3 |
Richard | Hardware | Hardware A | 4 |
Richard | Hardware | Hardware B | 4 |
Duilio | Software | Software A | 2 |
Duilio | Software | Software B | 5 |
Duilio | Hardware | Hardware A | 1 |
Duilio | Hardware | Hardware B | 2 |
now, I need to compute some measure that provides the count of staff members by training level.
To achieve this task I built a series of measures for each sub-level of technology, since it's fairly easy to count unfilteredrows:
Level 1 Sub = CALCULATE(COUNTROWS(training_table),'training_table'[Training Level] = 1)
...
Level 5 Sub = CALCULATE(COUNTROWS(training_table),'training_table'[Training Level] = 5)
but when it comes to the technology level, it gets rough: I tried to group and summarize the table, but it seems very difficult to get a count of filtered grouped table by the average value of a calculated column. Moreover, I don't actually know if is it possible to build a measure that can return the number of staff member per level that can work both for technology and sub-technolgy.
edit: the training level of technology level is the rounded up average of sub-technology levels.
thank you for the help!
Solved! Go to Solution.
Hi @Anonymous
I'm not sure whether it is what you need, but you can try these Measures.
Level Count by Sub-Tech =
CALCULATE (
COUNT ( training_table[Training Level] ),
FILTER (
ALLEXCEPT ( training_table, training_table[Sub-Technology] ),
training_table[Training Level] = MAX ( training_table[Training Level] )
)
)
Avg Level Count by Tech =
VAR _value =
SUMMARIZE (
training_table,
"avg",
AVERAGEX (
VALUES ( training_table[Sub-Technology] ),
[Level Count by Sub-Tech]
)
)
RETURN
ROUND ( _value, 0 )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
I'm not sure whether it is what you need, but you can try these Measures.
Level Count by Sub-Tech =
CALCULATE (
COUNT ( training_table[Training Level] ),
FILTER (
ALLEXCEPT ( training_table, training_table[Sub-Technology] ),
training_table[Training Level] = MAX ( training_table[Training Level] )
)
)
Avg Level Count by Tech =
VAR _value =
SUMMARIZE (
training_table,
"avg",
AVERAGEX (
VALUES ( training_table[Sub-Technology] ),
[Level Count by Sub-Tech]
)
)
RETURN
ROUND ( _value, 0 )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That was it!
Thank you!
@Anonymous , can you share the expected output?
Sure!
I need to produce two stacked graphs both with number of sfaff per training level. lets say that we have 9 people in the staff:
where level of technology level is the rounded up average of the sub-techonology values
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |