Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sagadgreat
Helper I
Helper I

Eliminating Duplicate Lines with Table Masures in BI

Account NumberContract NumberOrder NumberItem NumberQty OrderedReleased Quantity
56263331123456332210700
2545633561234663322150
56263331123476332250700
1236433861234863322832
5489933411234963322216

 

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
v-chenwuz-msft
Community Support
Community Support

Hi @sagadgreat ,

 

Is that you want this?

vchenwuzmsft_0-1656061673998.png

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.

 

View solution in original post

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @sagadgreat ,

 

Is that you want this?

vchenwuzmsft_0-1656061673998.png

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.

 

Checking now....

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.