Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a measure which returns the greatest contribution to our overall income as a value, but I can't figure out how you would get it to return the label. For example in this table, the measure would return the maximum value in the table of £150,123, but how do I get it to return the word 'Donations' instead?
Type | Actuals |
Legacies | 49,123 |
Events | 80,124 |
Donations | 150,123 |
The measure I have at the moment is:
Greatest Contributor =
MAXX(
KEEPFILTERS(VALUES('Grouping'[Type])),
CALCULATE([Actuals])
)
Thank you for your help datanauts!
Kaylee
Solved! Go to Solution.
@KSturt - This one should work:
Greatest Contributor =
MAXX(
FILTER(
'Grouping',
[Actuals] = MAX('Grouping'[Actuals])
),
[Type]
)
It first filters the grouping table to only keep rows which equal the Maximum. Then it finds the max type.
You could add a grouping step, in case you need to sum the values by type first:
Greatest Contributor =
var _summarized = SUMMARIZE('Grouping', 'Grouping'[Type], "Sum Actuals", SUM('Grouping'[Actuals]))
return MAXX(
FILTER(
_summarized,
[Sum Actuals] = MAXX(_summarized,[Sum Actuals])
),
[Type]
)
Thank you
@KSturt - This one should work:
Greatest Contributor =
MAXX(
FILTER(
'Grouping',
[Actuals] = MAX('Grouping'[Actuals])
),
[Type]
)
It first filters the grouping table to only keep rows which equal the Maximum. Then it finds the max type.
You could add a grouping step, in case you need to sum the values by type first:
Greatest Contributor =
var _summarized = SUMMARIZE('Grouping', 'Grouping'[Type], "Sum Actuals", SUM('Grouping'[Actuals]))
return MAXX(
FILTER(
_summarized,
[Sum Actuals] = MAXX(_summarized,[Sum Actuals])
),
[Type]
)
Marvellous! Thank you for the answer and promt reply 🙂 @Anonymous