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
Hi,
I have a somehow advanced analysis which cannot bring into a straight line of thinking when it comes to the question how to implement that in PBI. Maybe we you got a hint in which direction to think.
I have a table with orders:
Order | Date | Origin |
A | 1.1.2022 | 123 |
B | 1.1.2022 | 234 |
C | 1.1.2022 | 789 |
D | 1.2.2022 | 345 |
And I have a table with Leads which in Part resulted in these orders:
Lead | Date | Qualify |
123 | 1.1.2022 | TRUE |
234 | 1.12.2021 | TRUE |
345 | 1.12.2021 | TRUE |
456 | 1.12.2021 | FALSE |
567 | 1.1.2022 | FALSE |
678 | 1.1.2022 | FALSE |
789 | 1.1.2022 | TRUE |
What i want to do ist to calculate the average conversion rate of leads (qualify yes or no) of leads leading to the orders. The problem ist, that the origings of the orders occur in different times with different conversions.
I want to report the orders of January which is 3. And from that I want to calculate the conversion of every day which was participating in these orders and weight it by the amount of orders from that day.
So for january the orders came from leads on 1.1.2022 and 1.12.2021. So first of all I calculate the Conversion for these days:
1.1.2022: 2/4 =0.5
1.12.2022: 2/3= 0.66
the weight is 0.33 (1/3) for the 1.12.2021 and 0.67 (2/3) for the 1.1.2022
So my "average causing lead conversion rate" for these 3 orders is: 0.5 * 0.6 + 0.67 * 0.33 = 0.56
How to achieve this in a measure????😲
Solved! Go to Solution.
Hi @H3nning ,
Please follow these steps:
(1) Create a new measure and column
AVG =
VAR _TRUECOUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date]) && Leads[Qualify] = TRUE()))
VAR _COUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date])))
VAR _FALSECOUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date]) && Leads[Qualify] = FALSE()))
VAR _FALSECOUNT2 = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Qualify] = FALSE()))
RETURN (_TRUECOUNT/_COUNT)*(_FALSECOUNT/_FALSECOUNT2)
SUMX = SUMX(DISTINCT(SUMMARIZE(Leads,Leads[Date],Leads[AVG])),Leads[AVG])
(2)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HEUREKA!! 🙂
I think i got it. Thanks for pointing in the right direction. At least I understood now what earlier does!
First I created a column on the orders table referring to the date the corresponding Lead comes from:
_LeadDate = Related(Lead[Date])
Then with your hint I could bring the Conversion rate of Leads from that particular day to the orders table by creating another new column there:
_AVGLeadCR =
Thanks so much! I can't belive I finally understood Earlier!!!!💥
@v-jialluo-msft But maybe you can answer one question. Why do I need the FILTER function inside the calculate function? For example here:
CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date]=EARLIEST(Orders[_LeadDate])))
I first tried it without the FILTER(ALL(Leads),...) and gave the filter criteria to the calculate function instead. But then it seemed it did not use them for the countrows statement. Why?
HEUREKA!! 🙂
I think i got it. Thanks for pointing in the right direction. At least I understood now what earlier does!
First I created a column on the orders table referring to the date the corresponding Lead comes from:
_LeadDate = Related(Lead[Date])
Then with your hint I could bring the Conversion rate of Leads from that particular day to the orders table by creating another new column there:
_AVGLeadCR =
Thanks so much! I can't belive I finally understood Earlier!!!!💥
Hi @v-jialluo-msft , thanks a lot for your input. Im not totally understandig what you did. I adapted this solution on a greater scale, but it seems it does not sum up correctly. Does your solution only workl for month?
when looking for Quarters or years the weighting does not adjust. Is it possible to do this?
Hi @H3nning ,
Please follow these steps:
(1) Create a new measure and column
AVG =
VAR _TRUECOUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date]) && Leads[Qualify] = TRUE()))
VAR _COUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date])))
VAR _FALSECOUNT = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date] = EARLIER(Leads[Date]) && Leads[Qualify] = FALSE()))
VAR _FALSECOUNT2 = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Qualify] = FALSE()))
RETURN (_TRUECOUNT/_COUNT)*(_FALSECOUNT/_FALSECOUNT2)
SUMX = SUMX(DISTINCT(SUMMARIZE(Leads,Leads[Date],Leads[AVG])),Leads[AVG])
(2)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What I tried so far is this:
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 |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |