cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
henkka
Frequent Visitor

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

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]))
henkka_0-1660153840700.png

 

1 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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

henkka_0-1660197669558.png

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

henkka_0-1660226696076.png

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MahyarTF
Solution Sage
Solution Sage

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])
                                    )
                    )
MahyarTF_0-1660179396800.png

 

Appreciate for Kudos

henkka
Frequent Visitor

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

henkka_1-1660198126260.png

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors