topic Re: Min/Max/Avg columns for layered matrix visual in DAX Commands and Tips

Min/Max/Avg columns for layered matrix visual

Anonymous — Tue, 14 Nov 2023 20:26:12 GMT

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])

Re: Min/Max/Avg columns for layered matrix visual

mohgh — Wed, 15 Nov 2023 06:18:15 GMT

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

same one with MAX syntax would be desirable also!

Re: Min/Max/Avg columns for layered matrix visual

v-zhangti — Thu, 16 Nov 2023 07:29:01 GMT

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.

Re: Min/Max/Avg columns for layered matrix visual

Anonymous — Thu, 16 Nov 2023 13:48:36 GMT

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.

Re: Min/Max/Avg columns for layered matrix visual

v-zhangti — Fri, 17 Nov 2023 01:13:19 GMT

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.

Re: Min/Max/Avg columns for layered matrix visual

Anonymous — Tue, 21 Nov 2023 13:07:57 GMT

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