Forum Discussion
Dynamically calculate maximum cell value in matrix visualization
Hi Anonymous ,
The problem I'd been having was that I would get different bubble sizes for the same value on different rows. Using ALLSELECTED('table') with no columns specified seems to accomplish what I wanted.
Hi Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create the measures as below:
SRevenue = SUM('Sales'[Revenue])Chart =
VAR MAXR =
MAXX (
FILTER (
ALLSELECTED ( 'Sales' ),
'Sales'[Product Type] = SELECTEDVALUE ( 'Sales'[Product Type] )
&& 'Sales'[Item] = SELECTEDVALUE ( 'Sales'[Item] )
),
[SRevenue]
)
RETURN
"data:image/svg+xml;utf8," & "
<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'>
<line x1='0' y1='50' x2='100' y2='50' stroke='black' stroke-width='0.5' />
<line x1='50' y1='0' x2='50' y2='100' stroke='black' stroke-width='0.5' />
<circle cx='50' cy='50' r='" & 40 * [SRevenue] / MAXR & "' fill='"
& IF ( [SRevenue] > 20, "Darkcyan", "Tomato" ) & "'/>
<text x='50' y='50' font-size='20' text-anchor='middle' dominant-baseline='middle'>" & [SRevenue] & "</text>
</svg> "2. Create a matrix visual
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hello Anonymous and thank you for taking the time to reply to my question. I have downloaded the .pbix file you provided. It doesn't do what I'm looking for, though. To illustrate, I've modified your file to show three different scenarios:
- Maximum revenue value = [your MAXR calculation]
- Maximum revenue value = MAXX(ALL(Sales), [SRevenue])
- Maximum revenue value = MAXX(ALLSELECTED(Sales), [SRevenue])
I've provided 3 corresponding versions of the bubble chart you provided. All 3 work well enough if I select a row, but if any filters span multiple rows, the results are different.
The version using ALLSELECTED comes closest to what I want, which is to maximize the largest bubble size based on the current filter context.
Here's a link to my version:
https://1drv.ms/u/s!AgIj2L_vt8WrhtVK67K3pz6l6b2rSw?e=hCMxNe
Hi Anonymous ,
Thank you for the further clarification, did you get the final result you wanted? Is there anything else you need help with regarding this post? If not, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
Best Regards
Hi Anonymous ,
The problem I'd been having was that I would get different bubble sizes for the same value on different rows. Using ALLSELECTED('table') with no columns specified seems to accomplish what I wanted.