Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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,
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.
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.
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.
Yes but it is asked by the customer...
Thank you for the coloring
Show that information in a separate visual.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |