I have Create calculated Measure TOPN using sumx it does not bringing correct result.

[Nomi5007]

Hello guys
I need a little help regarding calculated measure. I have created calculated measure suing DAX SUMX function. 

I need to calculate top 10 employee by expense as a single measure. please see attached screen shot and help me whats wronge with my DAX expression. I am not getting any error either.

[v-danhe-msft]

Hi @Nomi5007,

Could you have checked if there is other filter for your data? Could you have tried use ALL() function in your formula? If it stlii could not work for you, could you please offer your sample data to have a test if possible?

Regards,

Daniel He

[Nomi5007]

Yes I have changed the expression like this

Top 10 employee by Expenses = SUMX(TOPN(10, ALL('Report 1'),'Report 1'[Legal Name]),[Total Amount])

got the same result. 

legal name and total amount comming from two different table joined on unique ID. 

I have added two files on google drive please download it from here

files

[v-danhe-msft · Accepted solution]

Hi @Nomi5007,

Based on my test, you could refer to below formula:

Measure2 = var temptable=TOPN(10,ALLSELECTED(Sheet1),CALCULATE(SUM('Report 1'[$ Amount]),USERELATIONSHIP(Sheet1[Worker ID],'Report 1'[worker ID])))
return
SUMX(temptable,CALCULATE(SUM('Report 1'[$ Amount]),USERELATIONSHIP(Sheet1[Worker ID],'Report 1'[worker ID])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Nomi5007]

Thank you it works