Search code examples
powerbireport

Remove duplicate using measure in power BI

ID  Product Amount
1   Milk    10
2   Orange  15
3   Tin     9
1   Milk    10
3   Tin     10
2   Orange  15
1   Milk    10
1   Milk    12
3   Tin     9
4   Mango   23
3   Tin     18

Remove duplicate using measure in power BI I have the above data and I want to Sum the Unique Amount base on product. There are duplicate amount and product and I was ask not to sum the duplicate and to make matter worse they don’t want me to remove the duplicate from the table. I am trying to use a measure that will exclude duplicate for example for Milk the amount are 10,10,10 and 12 and since the 10s are duplicate I need only one 10 and add it to 12 Milk will be 22 For Orange value are 15, 15, then I Only want 15. For Tin I want 10+9+18 then Tin 37 And Mango 23

expected output I tried Sum no luck

enter image description here

Solution

  • You can use SUMMARIZE function to group data and eliminate the duplicates. Assuming your table name is "Data":

    Total Amount = 
SUMX(
   SUMMARIZE(Data, Data[ID], Data[Product], Data[Amount]),
   Data[Amount]
)

    enter image description here