Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Min/Max/Avg columns for layered matrix visual

I would like to create a min/max column for the matrix picture below. My goal given this example would be to show the min/max LPW (orders per week) for each customer (blacked out) given the totals for the order id/Bid Id (30053777 and 30051653 in this example).

This would show 124 for max, and 15 for min, but it would be showing at the customer column and without needing to drill down do the actual order id level, similar to how the AVG. LPW column is showing 69 at the customer level given the order id values of 124 and 15. 

Table name is 'Bid Data' 
Current formula for average column: 

average = SUM('Bid Data'[LPW_CUST_ESTIMATE]) / DISTINCTCOUNT('Bid Data'[Bid ID])

 

 

  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Max = 
Var _Max=CALCULATE(MAX('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Max)
    Min = 
Var _Min=CALCULATE(MIN('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Min)
    Sum = 
Var _Sum=CALCULATE(SUM('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Sum)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • mohgh's avatar
    mohgh
    Regular Visitor
    2 years ago

    Min LPW = CALCULATE(MIN('Bid Data'[LPW_CUST_ESTIMATE]), ALLEXCEPT('Bid Data', 'Bid Data'[Customer])) 

     

     

    same one with MAX syntax would be desirable also!

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Measure:

    Max = 
Var _Max=CALCULATE(MAX('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Customer]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Max)
    Min = 
Var _Min=CALCULATE(MIN('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Customer]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Min)

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      The answer I am looking for would be:
                            Sum    Max    Min
      Customer 1   139      124      15

         30015153    15        15       15

         3005537      124      124     124

       

       

      The Customer 1 row specifically is the one I am trying to create this formula for, showing the min/max values of the individual order ID's for said customer.

      • v-zhangti's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        2 years ago

        Hi, Anonymous 

         

        You can try the following methods.

        Max = 
Var _Max=CALCULATE(MAX('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Max)
        Min = 
Var _Min=CALCULATE(MIN('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Min)
        Sum = 
Var _Sum=CALCULATE(SUM('Bid Data'[LPW_CUST_ESTIMATE]),ALLEXCEPT('Bid Data','Bid Data'[Customer],'Bid Data'[City State]))
Return
IF(ISINSCOPE('Bid Data'[Type]),SUM('Bid Data'[LPW_CUST_ESTIMATE]),_Sum)

        Is this the result you expect?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.