filtering a skill matrix

[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?

[vik0810]

Assuming your skills are numeric (if not, you can use @Greg_Deckler method to create a new calculated column) and the range is from 1 to 5 you can make a star rating with the measure

Avg Skill 1 = REPT(UNICHAR(9733), ROUND(AVERAGE(Skillz[skillz 1]),0))
&
REPT(UNICHAR(9734), 5-ROUND(AVERAGE(Skillz[skillz 1]),0))

For max use function MAX instead of AVERAGE.

If the range goes say to 4 replace the 5 in the measure with 4.

You result could look like:

[hummes]

Thanks for your help.. I manged to draw a quite nice and comprehensive spider chart.

I have countries as the "pillars" of the web and have the average and max skill values as "y-axxis" values.

I now want to add a slicer to make this less complex to view. But l ran into the following problems

1. I want to select "Skill 1" in a list and the average and max value should be displayed in the chart. So 1 entry in the slicer controlling 2 dataset on the chart -> is this possible?

2. I have no data field to use as a "skill list" to select from. How can I create such a list for a slicer.

I tried to add a column "Skills" with all the skills as rows. But this is not linked to my data. How to manage this?

[Ashish_Mathur]

Hi @hummes,

Your data is not well structured.  It should be a 4 column dataset - Name, Country. Skills and Value.  You should select the first two columns in the Query Editor and then unpivot the others columns.  Then you will be able to use slicers.

Hope this helps.

[hummes]

Yes you are right. But i get the data like this.
Ist there any Nice way to restructure the data automatical?

[Ashish_Mathur]

Hi,

Just Google for "unpivot data in power bi desktop"

[hummes]

OR:

Is it possible to configure the legend of a chart in such way that only the selected (multiselected) item is displayed?

Default behaviour is "fading out the others" which is not sufficient.

[Greg_Deckler]

A very hacky way of doing this would be to create new columns for each skill that are numeric:

skillz 1 = SWITCH([skill 1],"+",1,"++",2,"+++",3,"++++",4)

You could create a measure like so:

Skillz 1 Average = SWITCH(ROUND(AVERAGE(Skillz[skillz 1]),1),1,"+",2,"++",3,"+++",4,"++++")