Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum by distinct ID from duplicated rows

I have a non aggregated table that is transactional based on event that has duplicated ID's and Amounts similar to column a/b and I would like to have a measure to apply in my visualizations that shows the uniqe amount by order ID and then I can roll up to Vendor.   How would I write a formula to do this on the report side?  One of my attempts is:  

vsum = CALCULATE(sum('TABLE_1'[Amount]), ALLSELECTED(MAX('TABLE_1'[OrderID])))

 

Order IDVendorAmountIDVendorAmount
417306A67.80417306A67.80
417306A67.80503477B269.85
417306A67.80525849C204.53
417306A67.80   
417306A67.80   
417306A67.80   
417306A67.80   
417306A67.80   
503477B269.85   
503477B269.85   
503477B269.85   
503477B269.85   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
525849C204.53   
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello Anonymous 

    Give this a try, you will just have to change the name of your table in the measure.

     

    Measure = 
VAR XTable = SUMMARIZE(Table1,Table1[Vendor],Table1[Order ID])
RETURN
SUMX(XTable,MAX(Table1[Amount]))

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello Anonymous 

    Give this a try, you will just have to change the name of your table in the measure.

     

    Measure = 
VAR XTable = SUMMARIZE(Table1,Table1[Vendor],Table1[Order ID])
RETURN
SUMX(XTable,MAX(Table1[Amount]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you jdbuchanan71  for the quick reply, this gave me exactly what I was looking for.