Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
H3nning
Resolver I
Resolver I

Weighting, and Conversion in just one measure

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:

OrderDateOrigin
A1.1.2022123
B1.1.2022234
C1.1.2022789
D1.2.2022345

 

And I have a table with Leads which in Part resulted in these orders:

LeadDateQualify
1231.1.2022TRUE
2341.12.2021TRUE
3451.12.2021TRUE
4561.12.2021FALSE
5671.1.2022FALSE
6781.1.2022FALSE
7891.1.2022TRUE

 

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

2 ACCEPTED SOLUTIONS
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670824945542.png

 

 

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.

View solution in original post

H3nning
Resolver I
Resolver I

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:
 
H3nning_0-1671027167152.png

 

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

 

View solution in original post

5 REPLIES 5
H3nning
Resolver I
Resolver I

@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
Resolver I
Resolver I

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:
 
H3nning_0-1671027167152.png

 

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

 

H3nning
Resolver I
Resolver I

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?

H3nning_0-1671021016508.png

 

when looking for Quarters or years the weighting does not adjust. Is it possible to do this?

 

v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670824945542.png

 

 

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
Resolver I
Resolver I

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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.