Matrix - sum of max filter values

[stizein]

I have an excel file that contains records that may be associated with multiple branch locations.  I want to display the aggregate $ amount for the account in a matrix.  How do I best approach this?  Sum on the max value?  Summing the detail records would inflate the $ amount.  The user would like to filter on the branch.  Thanks in advance for the direction.

[v-yiruan-msft]

Hi @stizein ,

Whether your problem has been resolved? If no, you can try to create the below two measures. And apply [Measure 2] in Values field of your matrix. Otherwise, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Measure =
CALCULATE (
    MAX ( 'Table'[Commission] ),
    FILTER ( 'Table', 'Table'[Branch] = MAX ( 'Table'[Branch] ) )
)

Measure 2 = SUMX ( VALUES ( 'Table'[Branch] ), [Measure] )

Best Regards

[MFelix]

Hi @stizein ,

If the values are repeated in all the records you can use the MAX / MIN / Average value that this would do the result you need for a single branch, however be carefull because the total values will also assume that.

So if you want to show the total by branch and then the total of all branches you need to use a SUMX with the usage of MAX, MIN or AVERAGE within a summarized measure.

This also depends on what is the calculation you need to what I can see the duplication of values comes in the line so you can do a similar measure to this one:

Measure = sumx(SUMMARIZE('Table','Table'[Line],"@PremiumValue",MAX('Table'[EB Premium])),[@PremiumValue])

You need to do one measuree for each of the values you need to calculate