how to fix my measure which check if duplicates exists and sumx value based on the result

[henkka]

From the table I need to check if duplicate employee exists. If yes, then find the largest wage.id for the employee and then return the wage. if no duplicates, then return the wage. I have a measure, which works fine otherwise but with duplicate employee Id's the measure returns the sumx of value * 2. For example my table has duplicate id and the newest wage is 3600 but measure sums it wise ending to result 7200.

Monthlywage =

 SUMX (

     'Palkka',

     VAR currentEmployee = 'Palkka'[Employee.id]

     VAR summaryTable =

         TOPN (

             1,

             FILTER (

                 'Palkka',

                 'Palkka'[Employee.id] = currentEmployee

             ),

             'Palkka'[workagreement.id], DESC,

             'Palkka'[wage.id], DESC

         )

     RETURN

         SUMX(summaryTable,'Palkka'[wage]))

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[henkka]

Hi @Ashish_Mathur and thank you for the suggestion! Your measure seems to be working otherwise but the total is not calculated correctly 

[Ashish_Mathur · Accepted solution]

You are welcome.  Modify the measure to

Measure = SUMX(VALUES(Data[Employee.id]),CALCULATE([Wages],FILTER(VALUES(Data[wage.id]),[Highest wage id]=[Highest wage across all wage id's])))

Hope this helps.

[henkka]

Hi, still something funny with the measure. I don't understand from where the total sum comes. Here is the tablle and expected results. So Total sum should be 27 649,30 but the measure total is 46 800.

[Ashish_Mathur]

Share the link from where i can download your PBI file.  Show the probelm there clearly and also show the expected result.

[henkka]

Sorry , this was my bad. I made a little mistake modifyin the measure a little bit. Your suggestion did the job as should and expected! Thank you very much!

[Ashish_Mathur]

You are welcome.

[MahyarTF]

Hi,

I create the below dax measure and it seems work :

MaxWage = CALCULATE(

                    sum(Sheet86[Wag]),

                    ALLEXCEPT(Sheet86,Sheet86[Emp ID]),

                              filter(all(sheet86),

                                     Sheet86[WageId] = max(Sheet86[WageId])

                                    )

                    )

Appreciate for Kudos

[henkka]

Hi @MahyarTF and thank you for the suggestion. This solution is otherwise working but the total is not calculated correctly