Average Spend Amount based on the top 5 spenders and based on overall department all employees spend

[kalkhudary]

Hi All, 

I want to generate a matrix table the presents the below. I have a filter for date and business unit in my dashboard. If I click on Technolgy Department, I see the below names since I added the topN filter to show the top 5 spenders based on spend amount. 

Employee Name	Spend Amount	Average Spend Amount	Overall Average Spend Amount
Zak	1000	500	300
Max	2000	1000	200
Bernard	5500	600	150
Leo	400	50	20
Sarah	600	250	100
Total	9500	3000	150

I am trying to dax 2 measures, one that look at the average of the Spend Amount based on the top 5 spenders and one that looks at the overall average spend amount based on All employees in the Technology Department.

Would appreciate any help in daxing the concept above.

[kalkhudary]

@Greg_Deckler Can you try based on the table above by instering employee names and spend amounts with 2 different business units and check the outcome. I can't insert a BI file here.

[Greg_Deckler]

@kalkhudary See PBIX below signature.

[kalkhudary]

@Greg_Deckler That sounds to work but what if I need to filter to a particular business unit and see its top 2 average, how will I embed it in the dax to read it this way instead of looking at all table. What can we change in the first var to allow it to calculate based on selected business unit 

   VAR __Table0 = ALL('Table')

    VAR __Table1 = TOPN( 2, __Table0, [Spend Amount] )

    VAR __Table2 = SUMMARIZE( __Table1, [Employee Name], "__Value", SUM( 'Table'[Spend Amount] ) )

    VAR __Result = AVERAGEX( __Table2, [__Value] )

RETURN

   __Result

[Greg_Deckler · Accepted solution]

@kalkhudary Use ALLSELECTED instead of ALL

[Greg_Deckler]

@kalkhudary Not sure since no sample data provided but maybe:

Average Spend Amount = 
  AVERAGEX ( DISTINCT( 'Table'[Employee Name] ), SUM( 'Table'[Spend Amount] )

Average Spend Amount Overall = 
  AVERAGEX ( ALL( 'Table'[Employee Name] ), SUM( 'Table'[Spend Amount] )

Assumes that these are used in a visual with a Top-N filter.

[kalkhudary]

@Greg_Deckler It is providing repititive values for both measures. Not sure what we may be missing. See snippet of the table and applied filters.

[Greg_Deckler]

@kalkhudary Tough to say. Really need sample data as text to recreate.