DAX Measure for Max Value by Category and/or Product

[sang_moneta]

I am trying to come up with a Measure that calculates the Max value of Diff% in the following visuals based on the level the data is displayed.

On below screen, the leftmost visual is my raw data in Table, basically Category, Product, Actual and Target columns.

The middle one is the things when viewed from Categories point of view and the last visual is on Category and Product.

I have created measures MAct%, MTar%, MDiff% that are working fine on the last two visuals fine.

MAct% = 100*sum('Table'[Actual])/CALCULATE(sum('Table'[Actual]), ALL('Table'))

MTar% = 100*sum('Table'[Target])/CALCULATE(sum('Table'[Target]), ALL('Table'))

MDiff% = [MTar%]-[MAct%]

The MMAxDiff% is supposed to bring me the max of MDiff% on both the visuals, as displayed by red circle.

MMaxDiff% = maxx('Table', [MDiff%])

I have tried several ways but couldn't get to it. Please help. Here is the Power BI file for easy testing. https://www.dropbox.com/s/q08c64vb8ijkop0/TestProblem.pbix?dl=0 

[AntrikshSharma]

Try this:

MMaxDiff% =
MAXX (
    VALUES ( 'Table'[Product] ),
    CALCULATE ( [MDiff%], ALL ( 'Table'[Product] ) )
)

 

[stevedep]

This is your code:

MDiff% = MAXX(VALUES('Table'[Product]);[MTar%]-[MAct%])

As seen here:

Pls mark as solution if this works for you. Thumbs up for the effort are appreciated.

Kind regards, Steve. 

[sang_moneta]

This one was pretty nice. I was looking for that filter that can tell me if my visual was on a certail details level. Thank you Allison.

I added bit of real world complexity to the problem and the new book is over here. 

There is a Company dimension as well to the data and everything needs to be seen from Companies perspective. The idea is the Diff% if above a certain threshold, it should be higlited in default view. Which I could do using the Diff%. But also allow users to filter companies in following ways:

1.  See all of them and exceptions highlited on particular rows, that are higher than threshold.

2. See only Companies with exceptions, still exceptions highlited,

3. See companies without any exceptions.

MDiff%-Threshold = [MDiff%] - SELECTEDVALUE('ThresholdDiff%'[ThresholdDiff%])

 MDiff%-Threshold measure allows to do the highligting based on the selection done to the table ThresholdDiff% one column table, with values ranging from 1 to 10.

I asked for calculation of MMaxDiff% so the whole Company can be marked as having an exception or not.

So inspired from Allison's code my MMaxDiff% is:

MMaxDiff% = IF(HASONEVALUE('Table'[Pro]),
                CALCULATE(maxx(SUMMARIZE('Table', [Comp],[Cat],[Pro]), [MDiff%]), ALL('Table'[Cat]), ALL('Table'[Pro])),
                CALCULATE(maxx(SUMMARIZE('Table', [Comp],[Cat]), [MDiff%]), ALL('Table'[Cat])))

Before we allow users to filter it, it still needs to get checked against the ThresholdDiff%:

ShowCompaniesWithException = if(([MMaxDiff%] - SELECTEDVALUE('ThresholdDiff%'[ThresholdDiff%]))>0, 1, 0)

So, finally ShowCompaniesWithException allows user to filter in one of the 3 ways described earlier. But one thing, is this filter is based of a Measure, so it can not be placed in page filters or all page filters, and only on visual filters.

Following image is what I am trying to explain above. As you can see, Company 2 is not shown on both the visuals as they are only showing Companies that have Exceptions.

Is there a better way to do all this?

[AllisonKennedy · Accepted solution]

You're close, but 'Table' is the part you need to recreate based on the visuals.

Do you want the red circled number for all the rows in the far right visual or do you want it to change based on category?

Try these two options to see what I mean:
MMaxDiff% = IF(HASONEVALUE('Table'[Product]),CALCULATE(maxx(VALUES('Table'[Product]), [MDiff%]), ALL('Table'[Product])),CALCULATE(maxx(VALUES('Table'[Category]), [MDiff%]), ALL('Table'[Category])))

MMaxDiff% = IF(HASONEVALUE('Table'[Product]),CALCULATE(maxx(VALUES('Table'[Product]), [MDiff%]), ALL('Table')),CALCULATE(maxx(VALUES('Table'[Category]), [MDiff%]), ALL('Table'[Category])))

If you want 2.09 for the Category level as well in the far left visual, you may need to create separate measures.