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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Order #'s with multiple rows and products

Hello,

I have a report that I need to be able to summarize the number of orders with and the percentage of orders that meet 2 conditions. The formula I used to assign values to orders that meet these conditions is 

Perfect Order = IF(vwOrderInvoiceShipDetails[Short Shipped]="No" && vwOrderInvoiceShipDetails[invoice_lag]="On Time", 1, 0).
 
I am then taking the distinctcount of the order numbers then dividing the results from the Perfect order column by the distinct count. My issue is that due to there being multiple products for each order number my numerator is too large. If there are three items associated with the order the numerator would be 3 instead of the 1 it should be.

I can fix the issue if I remove duplicates in the query editor, but I still need to show the products that were shipped so I am unable to remove the duplicates. Any ideas on how I could keep the line items but make sure the original formula won't assign values greater than 1?
 
Thanks!
 
1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @Anonymous ,

not sure if I understand you correctly, but maybe this is doing what you are looking for:

Measure =
DIVIDE(
SUMX(
VALUES('<yourtablenema>'[ordernumber])
, calculate( average( '<yourtablenema>'[perfectoder] ) , '<yourtablenema>'[perfectoder] = 1 )
)
,[the distinctcountmeasure]
)

Hopefully, this provides what you are looking for.

If not, consider creating a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @Anonymous ,

not sure if I understand you correctly, but maybe this is doing what you are looking for:

Measure =
DIVIDE(
SUMX(
VALUES('<yourtablenema>'[ordernumber])
, calculate( average( '<yourtablenema>'[perfectoder] ) , '<yourtablenema>'[perfectoder] = 1 )
)
,[the distinctcountmeasure]
)

Hopefully, this provides what you are looking for.

If not, consider creating a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

That seems to have worked. Thank you Tom!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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