Find summed max in range with corresponding values

[shaunguyver]

I have a data table with the following columns:

- Risk ID (whole number)

- Building (text)

- Risk Impact (decimal)

The table has multuple entries. Each Risk ID is unique, each Building is not, and the Risk Impact is any random decimal number.

I want to create some simple measures:

1) A measure which returns the name of the Building with the highest combined Risk Impact value

2) A measure which returns the Risk Impact value of the above Building

3) A measure which returns the number of entires associated with the above Building

Your help would be much appreciated!

[Greg_Deckler]

So, sample data would be super helpful. 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

That being said, you are probably looking at something like using SUMMARIZE by Building with a SUM on Risk Impact. You can then use ADDCOLUMNS to add a rank or just use MAXX to return the MAX value of your Risk Impact. Then you filter that same table by that number to return the building name. So, let's say:

Highest Risk Impact = 
VAR __table = SUMMARIZE('Table',[Building],"__RiskImpact",SUM([Risk Impact])
VAR __highest = MAXX(__table,[__RiskImpact])
RETURN __highest

Highest RI Building = 
VAR __table = SUMMARIZE('Table',[Building],"__RiskImpact",SUM([Risk Impact])
VAR __highest = MAXX(__table,[__RiskImpact])
RETURN MAXX(FILTER(__table,[__RiskImpact]=__highest),[Building])

Count of Entries =
COUNTX(FILTER(ALL('Table'),[Building] = [Highest RI Building]),[Risk ID])

Something along those lines.

[shaunguyver]

Thank you Greg - The first 2 measures worked perfectly, but the last one just returns the total number of entires in the data table. It doesn't filter based on the '[Highest RI Building]' measure.

When I replace this with a string for the building name, it works. So it looks like using a measure as a filter is the problem?

[Greg_Deckler]

Eh, just do the last one this way. That's what I get for trying to be clever.

Count of Entries =
VAR __table = SUMMARIZE('Table',[Building],"__RiskImpact",SUM([Risk Impact])
VAR __highest = MAXX(__table,[__RiskImpact])
VAR __building = MAXX(FILTER(__table,[__RiskImpact]=__highest),[Building])
COUNTX(FILTER(ALL('Table'),[Building] = __building),[Risk ID])

And you could possibly lose the ALL.

[shaunguyver · Accepted solution]

Thanks Greg - I had to put a 'RETURN' separator between the variables and the calculation to get it work, but now it does.

To be clear for everyone, here is the final result:

Count of Entries =
VAR __table = SUMMARIZE('Table',[Building],"__RiskImpact",SUM([Risk Impact]))
VAR __highest = MAXX(__table,[__RiskImpact])
VAR __building = MAXX(FILTER(__table,[__RiskImpact]=__highest),[Building])
RETURN
COUNTX(FILTER(ALL('Table'),[Building] = __building),[Risk ID])

[Greg_Deckler]

Crap, good call, missed that. That's what I get for coding without testing.