Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello altruist,
I need to find the inquiry to the application conversion rate for the current week in comparison to the inquiry in the same week last month. Here's a snapshot of the source table and the target table. Does anyone have any idea how to find the conversion rate?
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create the two calculated columns as below to get the special week number
StartdayofWeek = 'Table'[Date]
+ -1
* IF (
WEEKDAY ( 'Table'[Date] ) < 6,
WEEKDAY ( 'Table'[Date] ) + 1,
WEEKDAY ( 'Table'[Date] ) - 6
)
Week =
1 + WEEKNUM ( 'Table'[StartdayofWeek], 1 )
- WEEKNUM ( STARTOFMONTH ( 'Table'[StartdayofWeek] ), 1 )
2. Create a measure as below to get the conversion rate
Conversion rate =
VAR _appcount =
SUM ( 'Table'[Application Count] )
VAR _premonthinqcount =
CALCULATE (
SUM ( 'Table'[Inquiry Count] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Table'[Date].[Year] )
&& MONTH ( 'Table'[Date] )
= SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 1
&& 'Table'[Week] = SELECTEDVALUE ( 'Table'[Week] )
)
)
RETURN
DIVIDE ( _appcount, _premonthinqcount, 0 )
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create the two calculated columns as below to get the special week number
StartdayofWeek = 'Table'[Date]
+ -1
* IF (
WEEKDAY ( 'Table'[Date] ) < 6,
WEEKDAY ( 'Table'[Date] ) + 1,
WEEKDAY ( 'Table'[Date] ) - 6
)
Week =
1 + WEEKNUM ( 'Table'[StartdayofWeek], 1 )
- WEEKNUM ( STARTOFMONTH ( 'Table'[StartdayofWeek] ), 1 )
2. Create a measure as below to get the conversion rate
Conversion rate =
VAR _appcount =
SUM ( 'Table'[Application Count] )
VAR _premonthinqcount =
CALCULATE (
SUM ( 'Table'[Inquiry Count] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Table'[Date].[Year] )
&& MONTH ( 'Table'[Date] )
= SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 1
&& 'Table'[Week] = SELECTEDVALUE ( 'Table'[Week] )
)
)
RETURN
DIVIDE ( _appcount, _premonthinqcount, 0 )
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
@Anonymous , Creat a date table with the week
Create a Friday week from here
Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
Then
you need measures
Inquiry = Sum(Table[Count of Inquiry]
Applications= Sum(Table[Application Count]
Conversion = divide([Inquiry ],[Applications])
refer if needed
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |