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
GS_Developer
New Member

Order to quote conversion data DAX filter query

I have 2 tables coming from Business Central into Power BI Desktop - Sales Orders and Sales Quotes. Now, we want to get the quote to order conversion data for a specific time duration as per the user for this data. So currently these 2 tables are connected by their creation dates. I need help creating a DAX query that would count the number of sales quotes created within a specific time period - for example last 1 month and divide it up with the number of orders created within the same period. That way I would be able to get the conversion percentage. 

GS_Developer_0-1642274350975.png

salesOrders have the column orderDate and salesQuotes have documentDate.

Any help with the filter query would be appreciated. Thanks!

2 REPLIES 2
v-xiaotang
Community Support
Community Support

Hi @GS_Developer 

vxiaotang_0-1642662714327.png

Based on the data structure and desired outcome you shared, I create an example for your reference,

Sample

vxiaotang_1-1642662835777.png

in the sample, total for Sales Orders=81, total for Sales Quotes=52. so the result= 81/52.

create a measure:

Measure = 
    var _selectYear= VALUE(SELECTEDVALUE(Slicer[Year]))
    var _selectMonth= VALUE(SELECTEDVALUE(Slicer[Month]))
    var _SalesOrders= CALCULATE(SUM('Sales Orders'[value]),FILTER(ALL('Sales Orders'),MONTH('Sales Orders'[creation dates])=_selectMonth && YEAR('Sales Orders'[creation dates])= _selectYear))
    var _salesQuotes= CALCULATE(SUM('Sales Quotes'[value]),FILTER(ALL('Sales Quotes'),MONTH('Sales Quotes'[creation dates])=_selectMonth && YEAR('Sales Quotes'[creation dates])= _selectYear))
return 
    DIVIDE(_SalesOrders,_salesQuotes)

result

vxiaotang_2-1642663113270.png

 

Best Regards,

Community Support Team _Tang

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

parry2k
Super User
Super User

@GS_Developer you should add a calendar dimension in your model, you can follow my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio... to create one.

 

Have relationship with date dimension to your sales order and quote table, and in visualization use year/month/date from date dimension and count from other two tables and you will get the result

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.