SUM Revenue , distinct by Job NO

[Anonymous]

Dear Team

Need your Help

I have table of Revenue, what i want is Getting Total or SUM

but my problem is there are some detail which is similiar or duplicate due user double copy paste it .

how to calculate my revenue with distinct JOB NO.

Pls See my table below

right calculation is green Revenue

pls helpp in DAX or other way

Thanks

Syaiful

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Add an index column in Query Editor.

2)Try this measure:

New Revenue = 
SUMX (
    GROUPBY ( 'Table', 'Table'[Index], 'Table'[No Job] ),
    CALCULATE (
        IF (
            CALCULATE (
                MIN ( 'Table'[Index] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[No Job] IN FILTERS ( 'Table'[No Job] )
                )
            )
                <> MAX ( 'Table'[Index] ),
            BLANK (),
            MAX ( 'Table'[Revenue] )
        )
    )
)

3)The result shows:

Here is my tets pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Pragati11]

Hi @Anonymous ,

You can try creating a measure as follows:

Revenue_Sum = CALCULATE(

                                              SUM('Revenue'[Revenue]),

                                              FILTER(DISTINCT('Revenue'), 'Revenue'[No Job] = MAX('Revenue'[No Job])

                                                         )

                                              )

Test the above DAX if this works for you or not.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Thanks for ur Respond

But i still dont get it

would u share me on pbix file

[Pragati11]

Hi @Anonymous ,

Can you please try tagging the person in your comment? It's hard to judge who you are refrerring to in your comment.

Thanks,

Pragati

[v-gizhi-msft]

Hi,

I have attached my test pbix file in my previous reply.

See this:

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-gizhi_microsoft_com/EZasVZz5XkxMmfrDKFG145gB... 

Best Regards,

Giotto

[Mariusz]

Hi @Anonymous 

You can cleanse your data in Power Query by applying  group by No Booking, No Job and Max of Revenue

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Dax pls

Im new in DAX

[Pragati11]

Hi @Anonymous ,

Check the following link to start with how to create a measure and use th measure calculation I posted in my last reply.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati