This topic contains 7 replies, has 3 voices, and was last updated by 
-
Posts
-
I have a calculation I would like to build from two fact tables. I have a table that lists returns made and a table that lists purchases made. We can call them returns and sales for simplicity. My look up tables include a date table, item table, customer table, and a sales person table. The customer table ties both fact tables together by “sell-to #”, the date table uses shipment date, and the item table links by product #.
I would like to determine the percent of customers who have a return who make a purchase (or purchases) after they make a return. I am looking to determine if we are keeping the customer or forever losing them. Can anyone offer any guidance?
I would like to build this using DAX as I am not a Query person but I do use the Query editor as necessary. My dates will only go back to the beginning of 2013 but I have some future dates that require my date table to go through the end of this year.
You help is greatly appreciated.
For this task I recommend pages 152 – 161 (“Multiple Data Tables, Differing Granularity”, especially the section “Budget vs. Actuals”) of Rob and Avi’s book, “Power Pivot and Power BI”.
I have a slightly similar question regarding budgets/ actuals/ and “Flash” which is a adjusted forecast based on Actuals and projected budget. All my data comes from one database but management likes to look at the data in different ways. I’ve put all the data into PowerPivot living in one fact table but I want to be able to write some calculate functions that compare Actuals to budget, or year over year, or flash to budget.
Do the different versions (Budget, Actuals, Flash..) each need to live in their own fact table or can they all be in the same one and still be able to write the compare functions?
thanks,
Greg
Although I am not looking at your actual data, if the data are all in one fact table, that is probably OK.
Often the reason why separate fact tables are used is that actuals are stored at a different granularity than forecast (for example, actual values are stored by date and budget values stored by month).
Thanks Tom,
so if I wanted to write a calculate function to show me the difference between Actuals over budget, would I have to write the function for each item of a P&L? meaning would there need to be one for Units, another for Net Revenue, etc… OR could I write the calculate compare based on the different versions (actuals, budget….)?
thanks for the help. I was looking through Rob and Avi’s book but couldn’t find the answer.
I do not think you have to go one way or the other, but my thoughts are that doing the compare based on different versions would be easier.
I wrote this as my compare and it seems work but I would have to write it for all the P&L items. Is there a formula that would allow me to just subtract one version against another so no matter what “fact” I pull in a get the compare? I’m new to the calculate measure, thanks for the help
=CALCULATE([units],’Version'[version]=”Budget”)-CALCULATE([units],’Version'[version]=”Actuals”)
Much could be said, but in your case I believe it will eventually come down to writing formulas for all P&L items.
However, you might consider using Power Query to separate budgets and actuals into separate tables which would make the formulas shorter. As long as a “budgets” table and an “actuals” table have all of the same parent (lookup) tables and columns from the lookup tables are used in pivot rows, columns, filters and slicers you can add and subtract budget and actual measures even when they are derived from separate tables.
I could go on a while about creating measures like building blocks (legos), but for now I will stop here.
The forum ‘Power Pivot’ is closed to new topics and replies.