Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am trying to compute the percentage of Free to Paid Sales conversions for a particular period.
DATE | Free Sales | Paid Sales | Conv % |
5/1/2021 0:00 | 320 | 225 | 70% |
5/2/2021 0:00 | 289 | 194 | 67% |
5/3/2021 0:00 | 267 | 153 | 57% |
5/4/2021 0:00 | 237 | 142 | 60% |
5/5/2021 0:00 | 297 | 194 | 65% |
5/6/2021 0:00 | 306 | 134 | 44% |
5/7/2021 0:00 | 330 | 133 | 40% |
5/8/2021 0:00 | 310 | 120 | 39% |
5/9/2021 0:00 | 279 | 93 | 33% |
5/10/2021 0:00 | 320 | 112 | 35% |
5/11/2021 0:00 | 349 | 111 | 32% |
5/12/2021 0:00 | 323 | 127 | 39% |
5/13/2021 0:00 | 262 | 82 | 31% |
5/14/2021 0:00 | 389 | 101 | 26% |
5/15/2021 0:00 | 362 | 100 | 28% |
5/16/2021 0:00 | 285 | 74 | 26% |
5/17/2021 0:00 | 252 | 76 | 30% |
5/18/2021 0:00 | 260 | 72 | 28% |
5/19/2021 0:00 | 291 | 131 | 45% |
5/20/2021 0:00 | 273 | 64 | 23% |
Sales become paid after the threshold of 5 days. So Conversion % should exclude the last 5 days. Expected output is below.
DATE | Free Sales | Paid Sales | Conv % |
5/1/2021 0:00 | 320 | 225 | 70% |
5/2/2021 0:00 | 289 | 194 | 67% |
5/3/2021 0:00 | 267 | 153 | 57% |
5/4/2021 0:00 | 237 | 142 | 60% |
5/5/2021 0:00 | 297 | 194 | 65% |
5/6/2021 0:00 | 306 | 134 | 44% |
5/7/2021 0:00 | 330 | 133 | 40% |
5/8/2021 0:00 | 310 | 120 | 39% |
5/9/2021 0:00 | 279 | 93 | 33% |
5/10/2021 0:00 | 320 | 112 | 35% |
5/11/2021 0:00 | 349 | 111 | 32% |
5/12/2021 0:00 | 323 | 127 | 39% |
5/13/2021 0:00 | 262 | 82 | 31% |
5/14/2021 0:00 | 389 | 101 | 26% |
5/15/2021 0:00 | 362 | 100 | 28% |
5/16/2021 0:00 | 285 | 74 | 0% |
5/17/2021 0:00 | 252 | 76 | 0% |
5/18/2021 0:00 | 260 | 72 | 0% |
5/19/2021 0:00 | 291 | 131 | 0% |
5/20/2021 0:00 | 273 | 64 | 0% |
Below are the measure i have created.
1. FreeSales : SUM( DATA[Free_Trials])
2. Conversions : SUM(DATA[Conversions])
3. Conv % : Calculate ( DIVIDE( FreeSales/Conversions,0), DATESBETWEEN(DATA[DATE], STARTDATE, ENDDATE-5))
(P.S: STARTDATE & ENDDATE are the min & max values from the date slicer)
Conv % is not working properly . It giving same value for all the rows in the table. Please help to fix this issue.
Thanks in advance!
Hi,
In an MS Excel file, can you show your formula based working to calculate SalesCon %? Share the download link of that MS Excel file. Also, in a card visual, you can only show one figure. So that figure (SalesCon %) do you want to show on the card visual? Show that clearly in the MS Excel file.
i will prepare and update. thanks
maybe you can try this
Measure =
VAR _maxdate=CALCULATE(max('Table'[DATE]),all('Table'))
return if(max('Table'[DATE])>_maxdate-5,0,[paidsales]/[freesales])
Proud to be a Super User!
Thanks. but the total is giving 0. also when i use the measure in the card visual getting 0 too.
do you want to display the result in a card visual or a table? What's the expected output for a card visual?
Proud to be a Super User!
I need this to be shown in both table and card visual. In card visual it will show the averge conversion % value.
maybe you can create another measure
measure2=averagex(table, measure)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |