Forum Discussion

GS_Developer's avatar
GS_Developer
New Member
4 years ago

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...
Need Help
Show and Tell
v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi GS_Developer 

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

Sample

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

 

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.