Forum Discussion

garynorcrossmmc's avatar
garynorcrossmmc
Advocate IV
1 year ago
Solved

Narrative Measure Not Matching Correctly

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.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      garynorcrossmmc
      Advocate IV
      1 year ago

      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's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      garynorcrossmmc
      Advocate IV
      1 year ago

      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.