Forum Discussion

stizein's avatar
stizein
Icon for Helper I rankHelper I
5 years ago

Matrix - sum of max filter values

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.

 

 

Need Help
Show and Tell

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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