Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Gabry, thank you for your support, it works as intended.
Hello,
isn't as easy as:
MedianProduct = Maxx(Productstable, yourmedianformula)
HI Gabry,
How would you write the median formula? 😅
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.
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..
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
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |