Forum Discussion

pminnov's avatar
pminnov
Icon for Helper II rankHelper II
5 years ago
Solved

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

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. 

Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    5 years ago

    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

     

     

7 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      pminnov
      Icon for Helper II rankHelper II
      5 years ago

      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. 

    • pminnov's avatar
      pminnov
      Icon for Helper II rankHelper II
      5 years ago

      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.

       

      AttributeValue
      Peer-reviewed publications6
      Conference presentations21
      Funded grants or awards5
      Grant or award applications2
      Peer-reviewed publications6
      Conference presentations8
      Funded grants or awards2
      Grant or award applications2
      Funded grants or awards3
      Conference presentations7
      Peer-reviewed publications25
      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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

         

         

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.