sum the revenue of distinct value only

[sandeep_sharma]

I need to sum the revenue for cases that have "Over 90 days" status in status column. The challange here is that I have ticket numbers multiple times in first column. I want to sum only once for one ticket number. I dont want to remove duplicates hence need a dax....Please help

[Ahmedx]

write three measures, like this

[Anonymous]

Hi  @sandeep_sharma ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create measure.

Flag1 =
var _groupmin=
MINX(
    FILTER(ALL('Table'),
    'Table'[Ticket No.]=MAX('Table'[Ticket No.])&&'Table'[Revenue]=MAX('Table'[Revenue])&&
    'Table'[status]=MAX('Table'[status])),[Index])
var _if=
IF(
   MAX('Table'[Index])= _groupmin,1,0)
return
_if

Flag2 =
SUMX(
    FILTER(ALL('Table'),'Table'[status]="over 90 days"&&[Flag1]=1),[Revenue])

3. Result:

If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ahmedx]

what does the measure show?

[sandeep_sharma]

It shows 1887006 which is the sum of 875 twice

[sandeep_sharma]

thanks @Ahmedx for quick response...but not sure why this is not working in my sheet.....

[amitchandak]

@sandeep_sharma , Try like

Sumx(Summarize(Filter(Table, Table[Status]= "over 90 Days"), Table[Ticket No], Table[Revenue]), Table[Revenue])

[Ahmedx]