Forum Discussion

shaunguyver's avatar
shaunguyver
Icon for Helper III rankHelper III
7 years ago
Solved

Find summed max in range with corresponding values

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!

Need Help
Show and Tell
Tutorial Requests
  • shaunguyver's avatar
    shaunguyver
    7 years ago

    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])

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      shaunguyver
      Icon for Helper III rankHelper III
      7 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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.