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

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.

      • shaunguyver's avatar
        shaunguyver
        Icon for Helper III rankHelper III
        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])