Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to display values relative to the maximum value in a matrix visual.
The problem can be discribed with the following table images.
I have an input table with text columns "A" and "B" and a number column "Value". The maximum is marked in red. ("TABLE")
The "normal" matrix visual would look like "MATRIX".
The desired output "% OF MAX MATRIX" has the maximum value (40) displayed as 100% (red) and the rest of the values as "Value" devided by this maximum value.
The desired matrix visual should "listen" to page level filters and slicers.
I made some progress by using this DAX formula:
RelValue =
VAR MaxValue =
MAXX(
SUMMARIZE(
Table1;
Table1[A];
Table1[B];
"Amount";
CALCULATE(
SUMX(
ALL(Table1);
[Value]
)
)
);
[Amount])
RETURN
DIVIDE(SUM[Value];MaxValue;0)
But this does not work as it does not respect "external" or page level filters (because of ALL). Even the relative values are not correct.
And maybe there is an easier solution to the problem after all.
I hope I could make the problem understandable.
Can anybody help me with this?
For your interest: I need the "% of max" value to display circles as SVG images is a matrix visual - comparable to a heatmap in a matrix. So I need to calculate the radius in DAX to get the right circle radius.
Solved! Go to Solution.
Hi @lsteffens , try this:
test?
VAR T1 ?
GROUPBY(ALLSELECTED(Table1),Table1[A],Table1[B],"@SUM",SUMX(CURRENTGROUP(),[Value]))
VAR MaxValue
MAXX(T1,[@SUM])
devolución
DIVIDE(SUM(Table1[Value]),MaxValue,0)
@lsteffens ,
hey Take it as solution,
Measure =
Thanks both of you.
I think both solutions do what I expect.
It was very kind of you to help me with this - great! 😉
Hey @lsteffens
If it works for you..Please accept it as solution..
it will help others to find.
Regards,
Snandy
I think I can only accept one solution ... and I accepted the fist one as it was the faster answer.
And both are shown directly after each other after clicking "go to solution" ...
As I said ... thanks both of you ...
Sample source data as text please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi, can you try:
Hi, thanks for your answer.
It does what I need for the specific test data I provided.
But if I add a new row to the test data, so that there needs to be a sum in the matrix this is not going to work.
It's because the max value is still 40, but needs to be 45 (40 + 5) in the matrix visual (see image).
New test data (as text 😉 😞
A | B | Value |
A1 | B1 | 10 |
A1 | B2 | 20 |
A2 | B1 | 30 |
A2 | B2 | 40 |
A2 | B2 | 5 |
Result in Power BI:
So I need the max of the sum of the matrix fields, but I don't know how to do this.
Hi @lsteffens , try this:
test?
VAR T1 ?
GROUPBY(ALLSELECTED(Table1),Table1[A],Table1[B],"@SUM",SUMX(CURRENTGROUP(),[Value]))
VAR MaxValue
MAXX(T1,[@SUM])
devolución
DIVIDE(SUM(Table1[Value]),MaxValue,0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |