April 6, 2018 at 4:07 pm #9557
I ran into a problem and I can’t find any solution out there – not in the net nor in Rob’s book. I somehow think it is an easy one but I can’t get it solved. I am running on powerpivot v1 because of IT restrictions.
For demonstration purposes I attached an excel file which illustrates my issue (I used linked tables to create the data and used SQL to set up a calendar table with powerpivot. Then I created the relations).
So I basically have a fact table containing revenue data (Fct_Revenue). This is linked to a calendar dimension, a campaigngroup dimension, a customer dimension and a company dimension. Moreover I have another fact table that holds information about the behavior of customers and companies (Fct_Segmentation). This table is based on a classical RFM model that also shows the changes that occurred. Every 6 months a snapshot is made of the status of the RFM segmentation and saved with a date stamp. So it is basically a slowly changing dimension. However if I set it up as a dimension I am not able to calculate how many people for example have changed their monetary status per year as the segmentation is not directly related to the revenue data (See dimension “KPIs” in attached file). So my idea was to set it up as its own fact table. All measures that concern solely this fact table work fine. The problem arises when I want to combine revenue data to this.
So when I want to see how much revenue was generated by the different recency segments (1-4) I want powerpivot to filter the RFM segmentation table, take the corresponding customer IDs and companies IDs, go the customer and companies table, select the corresponding records and get the SUM of revenue of the Fct_Revenue table.
However it seems that relating the Fct_Segmentation table to the same dimensions as the Fct_Revenue table acts like a filter on Fct_Revenue.
CALCULATE(SUM(Fct_Revenue[Revenue])) does not filter at all when I have recency on the pivot table (which makes sense to me).
CALCULATE(SUM(Fct_Revenue[Revenue]),Fct_Segmentation) actually applies a filter but not the way I expected it to. When I start to delete the different relations I can see how the filter changes as I delete them.
What am I missing here?
I guess this is rather confusing. I hope the attached file makes it clearer.
Any help is greatly appreciated. Thanks!
Attachments:You must be logged in to view attached files.April 6, 2018 at 9:24 pm #9559
I converted your workbook from 2010 to 2016.
The model is not simple because it there is a many-to-many relationship between the revenue table and many of the dimension tables.
This is further complicated since each of the other dimension tables can potentially create a combined filtering effect which can offset the revenue amounts.
I had success using copies of the segmentation “fact” table as a bridge tables for the different dimensions.
TomApril 9, 2018 at 11:52 am #9560
good to hear from you again.
Ok, so it wasn’t just me not understanding how to work with two fact tables. I thought maybe I was just not getting it.
Would you mind explaining your answer in a bit more detail? Where do you see an m-to-n relationship between Fact_Revenue and the dimensions? And can you give me an example for which dimension you set up a copy of the segmentation table as bridge table and how the bridge table looked like? Sorry for the hassle as I can’t look into your workbook because I can not open it… (running on powerpivot v1).
I forgot to mention in my workbook that customers or companies can have a segmentation but never created any revenue (we call them leads).
Thanks for your kind help!
StefanApril 10, 2018 at 7:09 am #9564
OK. Then in the sample segmentation all rows are for leads.
Using typical relationships does not work with the given model. Calculating the revenue for the segmentation table looks like the formula below (basically, for each row in the segmentation table, try to find “matching” rows in the revenues table, based on Campaigngroup_ID, Campaign_ID, Customer_Private_ID, and the revenue date between the start and end dates of the segment):
= SUMX (
Fct_Revenue[Campaigngroup_ID] = Fct_Segmentation[Campaigngroup_ID]
&& Fct_Revenue[Campaign_ID] = Fct_Segmentation[Campaign_ID]
&& Fct_Revenue[Customer_Private_ID] = Fct_Segmentation[Customer_Private_ID]
&& Fct_Revenue[Date] >= Fct_Segmentation[Start_Date]
&& Fct_Revenue[Date] <= Fct_Segmentation[End_Date] ), Fct_Revenue[Revenue] ) See attached workbook.
Attachments:You must be logged in to view attached files.April 10, 2018 at 8:55 am #9566
thanks for that!
As I am not able to open the powerpivot data and field list in the workbook I tried to replicate the idea of your measure. However if I try a simple version of the measure
I get the error message that the value of the Customer_Private_ID in the Fct_Segmentation table cannot be determined in the current context.
You mentioned bridge tables earlier. Is there any connection to this error message?
Thanks a lot,
StefanApril 10, 2018 at 3:45 pm #9567
I was able to open your workbook from a camputer outside of our organisation. I get why the measure itself did not work. So if I get you right you basically extend the Fct_Segmentation table with the data from the other dimensions and revenue data table. To follow this approach I can’t use LOOKUPVALUE (because of powerpivot v1) but I can achieve this in SQL.
In my understanding this would mean that the end user can not use the usual dimensions to analyse the data but has to use the columns of the Fct_Segmentation table for dicing and slicing. Is that correct? And how would you proceed with a date table? Would you load a second table for the Fct_Segmentation table?
StefanApril 11, 2018 at 4:51 am #9573
I should have mentioned that the last workbook I attached was an Excel 2010 workbook instead of an Excel 2016 workbook.
Given the complexities introduced by the many-to-many relationships, and that some of the potentially filtering columns in the segmentation table were partially populated across rows, while at least one filtering column was completely populated, it just seemed that the last approach I took was the “easiest” to understand and predict.
I like your idea of using SQL to create the segmentation table.
Also, since the segmentation table represents a six month snapshot of the data, I wonder how useful a calendar table would be with a day granularity. I do not know how many “snapshot” dates are in the actual segmentation table, but I picture less than 20 (10 years). If such is the case, just a slicer on the snapshot date seems like it could suffice.
Another thought: since your company has reservations about upgrading Excel, have you looked into Power BI (which for many applications is free, and would preserve/leverage your DAX and data modeling skills learned with Power Pivot, plus, you would have access to all of the latest DAX and data modeling functionality)?
TomApril 13, 2018 at 10:35 am #9575
Sorry for my late reply but I am quite busy these days.
Why does the fact that the segmentation table is partially populated across rows, while at least one filtering column is completely populated cause trouble? I ran into this already using bridge tables but did not quite understand the reason for it…
Getting in the revenue data with SQL sounded easier than it actually is. The additional join lets run time explode. So I have to put some more thoughts into that.
Yes, you are right with what you are saying concerning the date table. I will follow this path…
I was finally able to convince IT to upgrade to v2. Yeah! 🙂 By the way we ran into the following issue – just in case you guys at powerpivotpro come across this: https://social.technet.microsoft.com/Forums/exchange/en-US/0b8c703e-ece9-45f9-9228-66eb6c145c1d/powerpivot-addin-installation-issue-could-not-find-a-part-of-the-path
You must be logged in to reply to this topic.