Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Maximum values based on two col

Hi all, 

I have a table called 'Group' with the following table structure:

GroupMonthValue
group 1150
group 11100
group 11175
group 12100
group 13100
group 13200
group 2175
group 21100


As a result, I need the maximum value based on group and month:

GroupMonthMax (value)
group 11175
group 12100
group 13200
group 21100


How do I create such a result table based on my Group table?

Thank you very much in advance!



Need Help

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Hi Anonymous 

    The following will work:

    The measure is this:

    Max Value = MAX('Table'[Value])

    You could just drag the Value column into the table, then select the dropdown and use Maximum, but I recommend against that. That creates an implicit measure, and creating an explicit measure is best practice.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello tobeggo1996,

    For your question, here are three ways to provide:

    One.

    1. Create a calculated column.

    Max_Value_Column = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Group]=EARLIER('Table'[Group])&&'Table'[Month]=EARLIER('Table'[Month])))

    2. Result.

    v-yangliu-msft_0-1605058251928.jpeg

    二.

    1. Create a measure.

    Max_Value_Measure = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Group],'Table'[Month]))

    2. Result.

    v-yangliu-msft_1-1605058251932.jpeg

    三.

    1. Create a calculated table.

    MonthMax_table = SUMMARIZE('Table','Table'[Group],'Table'[Month],"MonthMax_calculation_table",MAX('Table'[Value]))

    2. Result.

    v-yangliu-msft_2-1605058251933.png

    You can download the PBIX file from here.

    Best regards

    Liu Yang

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , you can create

    measure = max(Table[value]) and use in visual with group

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi edhans amitchandak , 

    thank you for your replies. Is it possible to create a new table with the resultset? I need this table for comapring the maximal value with the current value.

    Thank you in advance!

    • edhans's avatar
      edhans
      Community Champion
      5 years ago

      Hi Anonymous , I don't think you need a separate table. Look at this:

      To do this
      1) Add the Value field to your table and tell it to Don't Summarize. This will show all of the values without aggregating.

      2) Change your Max Value measure to this:

      Max Value = 
CALCULATE(
    MAX('Table'[Value]),
    REMOVEFILTERS('Table'[Value])
)

      3) Add a new Current vs Max Value measure:

      Current Value vs Max = 
VAR varCurrentValue = MAX('Table'[Value])
RETURN
[Max Value] - varCurrentValue

       

      If that is not what you need Anonymous, please provide the specific output you need, a screenshot from Excel is fine for expected output.