DAX - Get the highest Median value from multiple columns.

[TcT85]

Could anyone please help me create the dax formula.

Is there a way to get the highest median value from multiple products for a product/date?

Product	Serialnumber	Date	Time	Machine 1	Machine 2	Machine 3	Machine 4	Machine 5	Machine 6
A	11111	2024-05-03	09:20:01	16	2	27	5	7	7
A	11112	2024-05-03	10:20:01	15	2	10	6	8	8
A	11113	2024-05-03	11:20:01	16	1	16	5	7	7
A	11114	2024-05-03	12:20:01	15	2	18	6	8	8
A	11115	2024-05-03	13:20:01	16	1	10	5	7	7
A	11116	2024-05-03	14:20:01	14	2	11	6	8	8
A	11117	2024-05-03	15:20:01	17	1	12	5	7	7
A	11118	2024-05-03	16:20:01	15	2	11	6	8	8
A	11119	2024-05-03	17:20:01	16	1	12	5	7	7
A	11120	2024-05-03	18:20:01	15	2	13	6	8	8
A	11121	2024-05-03	19:20:01	13	1	35	5	7	7
A	11122	2024-05-03	20:20:01	15	2	25	6	8	8
A	11123	2024-05-03	21:20:01	16	1	14	5	7	7
			Median	15	2	13	5	7	7
			Highest Median	15

I'm in need of 2 following visualisation:

1. Highest median per product.

2. Highest median per product over a time period like weeks.

[TcT85]

Gabry, thank you for your support, it works as intended.

[Gabry]

Hello,

isn't as easy as:

MedianProduct = Maxx(Productstable, yourmedianformula)

[TcT85]

HI Gabry,

How would you write the median formula? 😅

[Gabry]

It depends on how is set up your model. Why do you have machines in the columns, is your data set that way?

If so you should unpivot those columns before calculate the median. 

If that was just the visualization but the data it's not in that format the formula should look like this

MedianFormula = calculate(median(table[values]), all(products))

This formula calculates the median of the values in the "values" column, disregarding any filters applied to the "products" table.

[TcT85]

Hi Gabry,

The data is set that way.

The product goes through 5 machines and we get the machine time for each machine.

We need to find the highest median time to be able to create a tact time..

[Gabry · Accepted solution]

Alright, if you prefer not to unpivot the columns (which might not be the best approach anyway), you can proceed as follows:

1)Create a measure for each machine that calculates the median, like this:

machine1Median = MEDIAN('Table (3)'[Machine 1])
machine2Median = MEDIAN('Table (3)'[Machine 2])

...and so on, for each machine you have.

2)Then, to find the maximum of the medians, you can use the following formula:

maxmedian = MAXX(
UNION(
SUMMARIZE('Table (3)', 'Table (3)'[Product], "Median", [machine1Median]),
SUMMARIZE('Table (3)', 'Table (3)'[Product], "Median", [machine2Median])

(...and so on, for each machine you have)

),
[Median]
)

3)Lastly, you can display the products and their corresponding maximum median in a table visual