Complicated Calculated Formula Powerpivot! Help please!

[DPHugo]

hi Everyone,

It has been a while now that I am looking for an answer at this question. Maybe someone here can help me.

I am trying to compute conversion rate on consultation that my employees do to prospective clients.

I have a big table with all my invoices. One unique ID per invoices, a date creation, the name of the product billed, the revenue associated and the id profile of the client. Each of my clients have an ID (I have also a Clients table with the ID clients and all his personal info).
I do free consultation in order to close those prospective clients. For tracking purpose, I bill a 0$ invoice to each clients who book a consult. I would like to do a distinct count on id_profile who have both a consult and a productA billed.
My goal is to know for example for month of March, I have N number of consults booked whose X number that has been closed within (2 days; 30 days and so on) following the consultation date.

you will find a sample of my data attached,

Thanks in advance,

If you need more information to solve that problem, I wont be very far,


Hugo

https://docs.google.com/spreadsheets/d/1PLyKI8rg9rFLzseh23fd2uYPd8wM5vI84hV7QU07Ngw/edit?pli=1#gid=0

 

[scottsen]

If you can get your data imported into powerpivot, I can probably help out.

 

[DPHugo]

If you can get your data imported into powerpivot, I can probably help out.

How do you want me to share my powerpivot data here?

	Invoiced table
id_invoiced_product	date_invoice_creation	Product_name	Revenues	id_Profile
111	1/1/2014	Consultation	- $	33
113	1/2/2014	Product_A	1 000 $	44
115	1/1/2014	Consultation	- $	55
118	1/2/2014	Product_A	1 000 $	33
121	1/1/2014	Consultation	- $	66
124	1/1/2014	Consultation	- $	77
132	1/1/2014	Consultation	- $	88
130	1/2/2014	Product_A	1 000 $	55
133	1/1/2014	Consultation	- $	99
136	1/2/2014	Product_A	1 000 $	66

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

		Customer profil Table
id_Profile	Name	LAst name	Prile creation date	Address
33
44
55
66
77
88
99
111
222
333

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

My goal is to compute how many consultation invoices were billed in april ==> Easy it is 6 (my denominator)
Much more complicated I also want to compute how much productA I sold, relate to these 6 consultation (it can happen in april or later (my numerator) (it is 3 here for id profile 33, 55, 66)
I do not know how I can filter that in my calculate formula.

Hope it helps,

Hugo

 

[scottsen]

Well, the workbook you posted doesn't have any data actually in power pivot. I wasn't super interested in doing that import.

One way to do this would be a calculated column IsWinFromConsult, something like:

=CALCULATE(COUNTROWS(Invoices),
FILTER(ALL(Invoices),
Invoices[id_Profile] = EARLIER(Invoices[id_Profile]) &&
Invoices[Revenues] = 0 && EARLIER(Invoices[Revenues]) > 0
)
)

 

[DPHugo]

Well, the workbook you posted doesn't have any data actually in power pivot. I wasn't super interested in doing that import.

One way to do this would be a calculated column IsWinFromConsult, something like:

=CALCULATE(COUNTROWS(Invoices),
FILTER(ALL(Invoices),
Invoices[id_Profile] = EARLIER(Invoices[id_Profile]) &&
Invoices[Revenues] = 0 && EARLIER(Invoices[Revenues]) > 0
)
)

Thanks you Scottsen. Earlier formula is definitly the solution to my problem.

I re-arranged the formula in order she fits my data:
Calculated Field 3:=CALCULATE(COUNTROWS(invoiced_products);filter(ALL(invoiced_products);invoiced_products[id_profile_invoiced]=earlier(invoiced_products[id_profile_invoiced])&&invoiced_products[Consult]=1&&earlier(invoiced_products[Packages]=1)))

where invoiced_products[Packages]=1 when the invoice is for my ProductA (0 when other product)
invoiced_products[Consult]=1 when the invoice is for a consultation

The formula is not working.
I get this message:
Semantic error: EARLIER / EARLIEST refers to an earlier row context which does not exist.

Do you have any idea to solve that!


Thank you so much!


Hugo

 

[scottsen]

Sounds like you are trying to do this in a measure (calculated field in 2013-speak). My intent was for this to be on a calculated column. If you need a measure we will have to figure out something else.

 

[DPHugo]

Actually It doesn not matter to my if it is a calculated field or a calculated column. I just want to compute how many customer profiles have an invoice for consultation and finally get a second invoice for product A
The part in my formula which doe not work is there &&invoiced_products[Consult]=1&&earlier(invoiced_products[Packages]=1)))

I still miss something...

 

[scottsen]

I would need more details on what "not work" means, and preferably a link to your excel workbook to take a look.

 

[DPHugo]

I would need more details on what "not work" means, and preferably a link to your excel workbook to take a look.

Hi Scottsen,

Thank you very much for your help.

I put a sample of my powerpivot data in a google doc. So my invoice table looks the same

https://docs.google.com/spreadsheets/d/1PR8-ljqLDG2FIVBtifzcwvhBdhDN02yBVeNlReC366g/edit?usp=sharing

Tell me if ou need more information

Thanks a lot I appreciate it.


Hugo

 

[scottsen]

Looks like the file isn't public. I have requested access.