Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |