Matrix representation with colors by items and measure

[clem312]

Hi,

I have a maatrix with Brands, items and a percentage. I need to display the matrix like this

For the moment I have it like this :

If you have an idea, 

PBIX File 

Data Extract 

[V-lianl-msft]

Hi @clem312 ,

I have made some attempts, but I can't get the results you want.The calculation in the matrix is based on the row level. Taking Items as the row will produce many null values.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[clem312]

Hi @V-lianl-msft 

In fact I am not so far from the result expected :

2 things I can't get :

The color on the percentage measure and a column with Industry average.

[lbendlin]

For the coloring you use the same measure (with the color name) as the basis of the conditional formatting for the category value and the category name 

Adding a column with industry average makes little sense since your lists of categories vary by brand.

[clem312]

Yes but it is asked by the customer...

Thank you for the coloring

[lbendlin]

Show that information in a separate visual.

[lbendlin]

So for each brand you want to show the top five items? You would do that by adding a ranked measure

rank = 
if(HASONEVALUE(Q72[item]),rankx(All(Q72[item]),[_Q72%]))

and then adding a visual filter as rank<6

But I have a hard time understanding your measures. Can you please explain the reason for using Calculate here ?

_Q72_Like = SUMX(Values(Q72[ID]), calculate(max(Q72[WEIGHT])))

Usually what you are trying to do  (if you don't like the matrix column hierarchy) can be achieved by creating a cross joined table 

Position = CROSSJOIN(GENERATESERIES(1,10),VALUES(Q72[marques.make_lib]))

and then creating two measures, one for the item title and one for the value

Title = 
var pos = SELECTEDVALUE(Position[Value])
var br = SELECTEDVALUE(Position[marques.make_lib])
var a = GROUPBY(Q72,Q72[item],"val",sumx(CURRENTGROUP(),[_Q72%]))
var b = TOPN(pos,a,[_Q72%],DESC)
var c = TOPN(1,b,[_Q72%],ASC)
return CONCATENATEX(c,[item])

but this fails because your measure contains extra CALCULATE steps and I don't understand how to recreate the measure logic here.

[clem312]

Hi @lbendlin 

Thank you for your reply.

I am using 

SUMX(Values(Q72[ID]), calculate(max(Q72[WEIGHT])))
because I must SUM weight but only one weight per ID. Maybe there is another way.

I have to issues left on that matrix.

One is the colors. I need to display one color for the couple item+measure so that people can see easily their brand situation for each category of products (which I call "items").

The second one is that I need to add a column named "Industry average" with the average off all brands.