Eliminating Duplicate Lines with Table Masures in BI

[sagadgreat]

Account Number	Contract Number	Order Number	Item Number	Qty Ordered	Released Quantity
5626	3331	12345	63322	10	700
25456	3356	12346	63322	1	50
5626	3331	12347	63322	50	700
12364	3386	12348	63322	8	32
54899	3341	12349	63322	2	16

I have gone through several iterations of summarize and max from these forums and still haven't found exactly what I need.  I am trying to create a risk assesment based on number of days remaining in a contract, and the total qty of parts purchased against a contract.  

I have gotten this to work with summarize, but for some reason, I can't connect back to the original data model to pull other elements of the table into a visual as needed without recieving duplication of the customer account, etc..  I beleive this is mainly due to it having to be a many to many relationship.

Essentially, the qty released per customer is a fixed number and the qty ordered must be summed to be added to it (ex: customer 5626 would be 760 total released and on order).  Take note that the same part number exists for multiple customers/contract numbers and the values should be specific to them as well (so customer 12364 should be a total of 40 in the end).  

The formula being used to summarize the table is:

Released Qty = SUMMARIZE('Sales Agreements','Sales Agreements'[ACCOUNT_NUMBER],'Sales Agreements'[ITEM_NUMBER],'Sales Agreements'[RELEASED_QUANTITY], "Qty Ordered", sum('Sales Agreements'[Current Order Qty])) 

There is an additional column that sums the two columns together. 

Any help is greatly appreciated.  

[v-chenwuz-msft · Accepted solution]

Hi @sagadgreat ,

Is that you want this?

If yes, please use this measure.

Measure = 
var _s = addcolumns(SUMMARIZE (
    'Sales Agreements',
    'Sales Agreements'[Account Number],
    'Sales Agreements'[Item Number],
    'Sales Agreements'[Released Quantity],
    "Qty Ordered", SUM ( 'Sales Agreements'[Qty Ordered] )
),"all",[Qty Ordered]+[Released Quantity])
return
SUMX(_s,[all])

If no, please share the output you want even if hand-draw.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[sagadgreat]

Checking now....

[sagadgreat]

That was the fix!  I had to vary it a bit for my overall dataset, but worked like a charm!