Hello all,
I have the following information
ProductionLine Metric Value
PL1 M1 1
PL1 M2 2
PL1 M3 3
PL2 M1 6
PL2 M2 5
PL2 M3 4
From the information, I shall report the metric with the maximum values of each ProductLine, which is supposed to be like this
PL1 M3 3
PL2 M1 6
I am stucked in how to dynamically select the metric. Could anyone give some help on this. Thank you very much.
Hi,
Group on ProductionLine. Add a Query Calculation called Val with the expression total([Value] for [Metric]). Add a second Query Calculation called Max with the expression maximum([Value] for [ProductionLine]). Add a filter with the expression [Val] = [Max] and set the timing to 'After auto aggregation'
MF.
Thank you very much MF. It worked perfectly. Instead of using maximum, I used rank to pick the top n, it accomodates users' need better.
However, I need further help if I could. How about this time, the table is a crasstab like this
REGION1 REGION2 REGION3 REGION4 REGION5 Total(REGION) ranking??
PL1 Metric1 1 1 1 1 1 5
Metric2 2 2 2 2 2 10
Metric3 3 3 3 3 3 15
PL2 Metric1 3 3 3 3 3 15
Metric2 2 2 2 2 2 10
Metric3 1 1 1 1 1 5
Can I still add an item to rank by Total(REGION), then pick the top 1 or 2 ?
Thank you.