Narrative Measure Not Matching Correctly

[garynorcrossmmc]

Hi all,

I have this table of values:

You'll see that the highlighted value of 20 for the Function 'Executive Assistants' is the highest value in the table.  I'm using the following metric to calculate the amount of the 'Employees - Receiving Special Bonus' that correspond with the value of the highest function of Executive Assistants for 2024:

Colleagues (Receiving Special Bonus) - Top Function # (TOPN) =
CALCULATE(
[Employees - Receiving Special Bonus],
TOPN ( 1, VALUES ( 'OWG Comp Listing'[Function] ), [Employees - Receiving Special Bonus], DESC ),
'OWG Comp Listing'[Year]=2024
)+0

Supporting Measures:

Employees - Receiving Special Bonus =
CALCULATE(
[Employees],
'OWG Comp Listing'[Special Bonus Amount]>0
)

Employees =
DISTINCTCOUNT('OWG Comp Listing'[Employee ID])

Special Bonus Amount =
DIVIDE(
SUM('OWG Comp Listing'[Special Bonus Amount]),
SELECTEDVALUE('Exchange Rates'[Year-End FX Rate]),
0
)

For some reason, the 'Colleagues (Receiving Special Bonus) - Top Function # (TOPN)' measure is returning an incorrect amount of 13.  What am I doing wrong here that I am not getting the correct number of 20?  Oddly enough, this method is working correctly elsewhere in the file.

[Anonymous · Accepted solution]

Hi, @garynorcrossmmc 

You can try the following methods.

Measure = MAXX(ALL('Table'),[Employees - Receiving Special Bonus])

Is this the result you expected?

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.

[garynorcrossmmc]

Thanks, @Anonymous!  This appears to now be returning the correct calculation with a slight modification:

Colleagues (Receiving Special Bonus) - Top Function # (TOPN) = 
MAXX(ALLSELECTED('OWG Comp Listing'[Function]),CALCULATE([Employees - Receiving Special Bonus],'OWG Comp Listing'[Year]=2024))+0

[rajendraongole1]

Hi @garynorcrossmmc - First, check that the granularity of the data is consistent with how you are trying to calculate the top value. If there are duplicates or variations in other columns, the TOPN may behave unexpectedly.

Modified measure:

Colleagues (Receiving Special Bonus) - Top Function # (TOPN) =
VAR TopFunction =
TOPN (
1,
SUMMARIZE(
'OWG Comp Listing',
'OWG Comp Listing'[Function],
"TotalEmployees", [Employees - Receiving Special Bonus]
),
[TotalEmployees],
DESC
)
RETURN
CALCULATE(
[Employees - Receiving Special Bonus],
FILTER(
'OWG Comp Listing',
'OWG Comp Listing'[Function] IN TopFunction &&
'OWG Comp Listing'[Year] = 2024
)
) + 0

Hope this helps.

[garynorcrossmmc]

Thanks for the reply, @rajendraongole1!  I'm receiving this error from the calc, do you have any recommendation on how to get around this?

The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.