topic Re: Dynamically calculate maximum cell value in matrix visualization in DAX Commands and Tips

Dynamically calculate maximum cell value in matrix visualization

Anonymous — Mon, 25 Jul 2022 20:12:04 GMT

Hello,
I am using the matrix visualization to create a matrix bubble chart using SVG, similar to this post Matrix Bubble Chart By DAX . The columns of the table are vendors, the rows are product type and item, and the summary value is revenue, as follows:

Product Type	Item	Vendor A	Vendor B	Vendor C
clothing	shoes	5	20
clothing	socks	10	40	30
groceries	apples	15		25
groceries	oranges	20		10

So the largest value in this table is 40.

I want to filter the table dynamically for different vendors using slicers, and to scale the maximum bubble size in the entire chart to the largest value in the filtered matrix.

I've been able to calculate the radius for the SVG circle using the following DAX:

Radius = VAR totalRevenue = SUM('Sales'[Revenue]) VAR maxRevenue = MAXX(ALLSELECTED('Sales'[Vendor]), totalRevenue) VAR circleRadius = DIVIDE(totalRevenue, maxRevenue, 0) RETURN circleRadius

The problem I'm having is that radius for the same value of totalRevenue is different on a row-by-row basis because of the MAXX function, which finds the maximum value for each row. If I try replacing MAXX with MAX, I get an error:

Function 'MAX' does not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

I thought perhaps I could create a dynamic table using slicers as the basis for my calculation. In searching this forum, though, I came across this post Create dynamic DAX table filtered by slicer from another table in which MattAllington says "You cannot create a dynamic table based on a slicer." I've read Matt's books and newsletters on DAX, and I trust that he knows what he's talking about.

Is there another way to do what I want?

Re: Dynamically calculate maximum cell value in matrix visualization

vapid128 — Mon, 25 Jul 2022 20:55:34 GMT

Product Type	Item	Vendor A	Vendor B	Vendor C
clothing	shoes	5	20
clothing	socks	10	40	30
groceries	apples	15		25
groceries	oranges	20		10

So the largest value in this table is 40.

measure =

MAXX(
SUMMARIZECOLUMNS(table[Product Type],table[Item],table[Vendor]),
CALCULATE([MEASURE FOR REVNUE])
)

=40

Re: Dynamically calculate maximum cell value in matrix visualization

Anonymous — Mon, 25 Jul 2022 21:20:34 GMT

Thanks for the reply vapid128 . I tried the solution suggested, but I'm getting an error:

Error Message: MdxScript(Model) (45, 6) Calculation error in measure 'Table'[Max Revenue]: SummarizeColumns() and AddMissingItems() may not be used in this context.

I should have also mentioned that, ideally, I would like to use a Fields Parameter to select the Row dimensions I want to display.

Re: Dynamically calculate maximum cell value in matrix visualization

vapid128 — Mon, 25 Jul 2022 21:33:14 GMT

Measure 4 = MAXX(SUMMARIZECOLUMNS('4Table'[Item],'4Table'[Product Type],'4Table'[Vendor]),CALCULATE(SUM('4Table'[Value])))

Re: Dynamically calculate maximum cell value in matrix visualization

Anonymous — Thu, 28 Jul 2022 07:43:10 GMT

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&colon;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

Re: Dynamically calculate maximum cell value in matrix visualization

Anonymous — Fri, 29 Jul 2022 01:19:06 GMT

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

Re: Dynamically calculate maximum cell value in matrix visualization

Anonymous — Mon, 01 Aug 2022 07:53:23 GMT

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

Re: Dynamically calculate maximum cell value in matrix visualization

Anonymous — Mon, 01 Aug 2022 17:31:31 GMT

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.