Min/Max/Avg columns for layered matrix visual

[Anonymous]

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]

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]

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 · Accepted solution]

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]

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

[mohgh]

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

same one with MAX syntax would be desirable also!