calculating average and highest value

[hummes]

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?

[v-jiascu-msft · Accepted 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

[hummes]

That's very helpfull. Thx.

Slowly getting into this 🙂

[jthomson]

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