cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Eliminating Duplicate Lines with Table Masures in BI

 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.
1 ACCEPTED SOLUTION
Community Support

Is that you want this?

If yes, please use this measure.

``````Measure =
'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.

3 REPLIES 3
Community Support

Is that you want this?

If yes, please use this measure.

``````Measure =
'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.

Helper I

Checking now....

Helper I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors