Hey there,
I have been stucked with a table for some weeks now and I do not know what going on there. I would be really grateful if somebody could take a look at it and give me feedback.
Analysis:
I am analyzing the conversion rate from unique quote to order. For that I have a facts table "Vorgänge" = transactions which includes all quotes and orders. A transaction in this table defined as the process from quote to order. Every Transaction has one row. Hence, Quote as well as order information are in one row.
Usually customers request several different items (every quoted items is a new transaction) but would just buy one of those quoted items. Hence I am counting 10 quotes from one customer in a certain time period as 1 quote.
For that I have created a pivot table which counts the number of orders that were quoted before (there are also orders without foregone quote) as well as the unique quotes (distinct count of contact IDs). Dividing those two gives me the conversion rate. As you see in my pivot table I get the right solution (17%) however, the unique contacts (measure) is not correctly allocated to the different time periods. As a result in every quarter I have conversion rates above 100% as 3150 contacts could not be allocated to a date.
Excel File
https://dl.dropboxusercontent.com/u/48053599/BI ****pit_V8.xlsx
Picture of Pivot Table:
https://dl.dropboxusercontent.com/u/48053599/Pivot Table.png
Seriously I would be so grateful if you could help me with that. I am getting really desperate with this as it seems totally unlogical to me.
the column names are in German. Sorry, I could not rename them without destroying all my formulae. I have created a table though where I have collected all the translations.
I have been stucked with a table for some weeks now and I do not know what going on there. I would be really grateful if somebody could take a look at it and give me feedback.
Analysis:
I am analyzing the conversion rate from unique quote to order. For that I have a facts table "Vorgänge" = transactions which includes all quotes and orders. A transaction in this table defined as the process from quote to order. Every Transaction has one row. Hence, Quote as well as order information are in one row.
Usually customers request several different items (every quoted items is a new transaction) but would just buy one of those quoted items. Hence I am counting 10 quotes from one customer in a certain time period as 1 quote.
For that I have created a pivot table which counts the number of orders that were quoted before (there are also orders without foregone quote) as well as the unique quotes (distinct count of contact IDs). Dividing those two gives me the conversion rate. As you see in my pivot table I get the right solution (17%) however, the unique contacts (measure) is not correctly allocated to the different time periods. As a result in every quarter I have conversion rates above 100% as 3150 contacts could not be allocated to a date.
Excel File
https://dl.dropboxusercontent.com/u/48053599/BI ****pit_V8.xlsx
Picture of Pivot Table:
https://dl.dropboxusercontent.com/u/48053599/Pivot Table.png
Seriously I would be so grateful if you could help me with that. I am getting really desperate with this as it seems totally unlogical to me.
the column names are in German. Sorry, I could not rename them without destroying all my formulae. I have created a table though where I have collected all the translations.
I used the wrong date dimension 
