Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the follwoing issue and would be happy about some advice.
I am building up a skill matrix for people and would like to have a nice representation of what is the average skill per country per skill.
For example this list should give me an average skill1 for USA of +++ and the highest is ++++
name | country | skill 1 | skill2 | skill 3 |
john | usa | ++++ | + | + |
jack | can | +++ | ++ | + |
jim | usa | +++ | + | ++++ |
julie | usa | + | + | +++ |
maria | ger | ++ | + | +++ |
frank | ger | ++++ | + | ++ |
Can someone help in calculating these numbers and hopefully having a nice graphical representation of skilllevel per country and skill at the end?
Solved! Go to Solution.
Hi @hummes,
1. In the Query Editor, select skill1 to skill 3 with "CTRL".
2. Unpivot only the select columns.
3. In the Data view, add a calculated column.
SkillScore = len([Value])
4. Add two measure.
SkillAverage = REPT ( "+", ROUNDUP ( AVERAGE ( Table1[SkillScore] ), 0 ) )
Highest = MAX ( 'Table1'[Value] )
You can try it here: https://1drv.ms/u/s!ArTqPk2pu-BkgUGcuY9MxpAkYsgK.
Best Regards!
Dale
Hi @hummes,
1. In the Query Editor, select skill1 to skill 3 with "CTRL".
2. Unpivot only the select columns.
3. In the Data view, add a calculated column.
SkillScore = len([Value])
4. Add two measure.
SkillAverage = REPT ( "+", ROUNDUP ( AVERAGE ( Table1[SkillScore] ), 0 ) )
Highest = MAX ( 'Table1'[Value] )
You can try it here: https://1drv.ms/u/s!ArTqPk2pu-BkgUGcuY9MxpAkYsgK.
Best Regards!
Dale
That's very helpfull. Thx.
Slowly getting into this 🙂
If that's how your data actually looks, it'd probably be best to use Text.Length in Power Query to get your data into a numerical format that'd be easier to manipulate, could also do similar with LEN in DAX if you prefer
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |