how to sum values in one column associated with values in another column and identify the max value

[pminnov]

Hi, 

I have a table called 'Replaced Value 3' that contains the following columns and values:

I need to calculate a sum value in the 'Value' column for each associated value in the 'Attribute' column. Then I need to use a card in a visual that will contain a formula that identifies the value in the 'Attribute' column that has the maximum sum value and retrieve that value (e.g., Conference presentations, 150). I've fairly new to Power BI and I believe I know what functions to use but it's the syntax that keeps tripping me up. 

Any help would be greatly appreciated. 

[edhans]

Glad to help @pminnov 

[edhans]

Again @pminnov provide data, and sample expected results per links below. A table and pic or two are worth thousands of words.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans]

Something like the following would work:

test Measure 1 =
SUMX(
    FILTER(
        VALUES( 'Replaced Value 3'[Attribute] ),
        'Replaced Value 3'[Attribute] = "Conference Presentations"
    ),
    'Replaced Value 3'[Values]
)

If you need more help @pminnov please include actual data we can test with per links below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[pminnov]

An excerpt from my data table is below. What I want is a way to return what value under the 'Attribute' column has the highest sum value under the 'Value' column as well as return what that sum value is (for use in a visual card). So the result that I would want to get from the table below would be: peer-reviewed publications and 37.

Attribute	Value
Peer-reviewed publications	6
Conference presentations	21
Funded grants or awards	5
Grant or award applications	2
Peer-reviewed publications	6
Conference presentations	8
Funded grants or awards	2
Grant or award applications	2
Funded grants or awards	3
Conference presentations	7
Peer-reviewed publications	25

[edhans · Accepted solution]

Here you go. I get this:

I used this measure, building one piece at a time. A card can only hold one value, so I had to concatenate the text and numerical value into one string.

Max Value For Card = 
VAR varSummaryTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[Attribute]
        ),
        "Total Value",
            CALCULATE(
                SUM( 'Table'[Value] )
            )
    )
VAR varMaxValue =
    MAXX(
        varSummaryTable,
        [Total Value]
    )
VAR varMaxRecord =
    FILTER(
        varSummaryTable,
        [Total Value] = varMaxValue
    )
VAR Result =
    MAXX(
        varMaxRecord,
        [Attribute]
    ) & " "
        & FORMAT(
            MAXX(
                varMaxRecord,
                [Total Value]
            ),
            "#,##0"
        )
RETURN
    Result

[pminnov]

Yes that worked - thank you very much!

[pminnov]

Hi, 

Thanks for the suggestion however what I want to do is have a visual card that contains text with a formula that will identify the most frequent attribute value based on the highest corresponding sum value under the 'Value' column. So for each attribute value (e.g., peer-reviewed publications, conferences) a sum value needs to be calculated for the 'Value' column and then the attribute value with the highest sum is presented in the card along with the associated numerical sum value (e.g., "conference presentations was the highest with 200 reported"). To do this I figured I would need to sum values in the 'Value' column for each value in the 'Attribute' column then create a measure using a max formula to identify the highest value in the 'Value' column and possibly another meaure that returns the associated value for that max from the 'Attribute' column.