Get relative of max value in a matrix visual (respecting filters)

[lsteffens]

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.

[sanalytics]

@lsteffens ,

hey Take it as solution,

Measure =

VAR _Max = MAXX(SUMMARIZE(ALLSELECTED('Table'),'Table'[A],'Table'[B],"Val",SUM('Table'[Value]) ),[Val])

Return

DIVIDE(SUM('Table'[Value]),_Max)

Hope it will helps you tol solve your problem

Regards,

Snandy

[lsteffens]

Thanks both of you.

I think both solutions do what I expect.

It was very kind of you to help me with this - great! 😉

[sanalytics]

Hey @lsteffens 
If it works  for you..Please accept it as solution..

it will help others to find.

Regards,

Snandy

[lsteffens]

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 ...

[Greg_Deckler]

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

[Vera_33]

Hi, can you try:

Test =

VAR MaxValue =

MAXX(ALLSELECTED(Table1),[Value])

RETURN

DIVIDE(SUM([Value),MaxValue,0)

[lsteffens]

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.

[Vera_33 · Accepted 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)