Weighting, and Conversion in just one measure

[H3nning]

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????😲

[v-jialluo-msft · Accepted 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.

[H3nning]

@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?

[H3nning · Accepted solution]

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 =

VAR Qual = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date]=EARLIEST(Order[_LeadDate]) && Leads[Qualify]=TRUE()))

VAR Total = CALCULATE(COUNTROWS(Leads),FILTER(ALL(Leads),Leads[Date]=EARLIEST(Orders[_LeadDate])))

Return

Qual/Total

When I bring this column to a table an select it should be calculated as an average, then I can slice and dice the orders as I like and I will alsways get the new calculated result:

Thanks so much! I can't belive I finally understood Earlier!!!!💥

[H3nning]

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?

[v-jialluo-msft · Accepted 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.

[H3nning]

What I tried so far is this:

Test =

VAR LeadDate = SUMMARIZE(Leads,Leads[Date]

                    ,"Anzahl Leads",DISTINCTCOUNTNOBLANK(Leads[Lead])

                    ,"Anzahl Qualifizierungen", CALCULATE(DISTINCTCOUNTNOBLANK(Leads[Lead]),Lead[Qualify]=TRUE)

                    ,"CR",CALCULATE(DISTINCTCOUNTNOBLANK(Leads[Lead]),Lead[Qualify]=TRUE) / DISTINCTCOUNTNOBLANK(Leads[Lead])

                    ,"Weight", Distinctcountnoblank(Orders[Order]) /  CALCULATE(distinctcountnoblank(Orders[Order]))

)

My goal was to build a table with the distinct rows for each date participating in the reported orders. For that i will still have to add some allexept, but that depend on the contexts where I will use it in and i havent implemented it yet. But all necessary numbers should be in place now. 

What I want to do is Sum(LeadDate[Weight] * LeadDate[CR], but that is not allowed because it is not a base table. How can I do this then?