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 fo...
  • 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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        in your example, what would [Type] refer to?