Forum Discussion
calculating average and highest value
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?
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
3 Replies
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
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 :-)
