This topic contains 9 replies, has 2 voices, and was last updated by 
-
Posts
-
Hello,
I’m working on a project to create a data model that tracks posted revenue. I’m getting an extract every morning (as a text file) that has posted revenue from the previous day. Currently we’re loading these text files into an excel workbook that has macros which update and combine data from other lookup tables on other sheets in the workbook.
I converted all of this work to powerpivot and powerquery. I just set up powerquery to grab these text files from a shared folder and load them into powerpivot after it transforms and cleans etc.
What I need help with is taking a table that has daily expected revenue by cost center and relating that back to my transactions that post everyday for posted revenue so that I can compare posted versus expected.
The expected revenue table was set up with days of the week as columns so I unpivoted that table so now each day of the week has it’s own row for every cost center. I’ve attached the table here.
I was thinking I should create a new column on the expected revenue table for day of week number but I’m not sure.
Thank you
Excellent to hear that you were able to simplify your data handling in this case using Power Query instead of VBA!
Regarding your thought about create a column for day of week number, based on only seeing a part of your data model, I do not think such a column will help. However, a column that shows the actual date, could offer more by way of relationship with a calendar (dates) table.
An interesting fact about Power Pivot models that have two (or more) fact tables is that when the fact tables share the same lookup tables (such as locations, dates, days-of-week, cost center, etc.) AND the shared lookup table columns are used as coordinates of a pivot, you can place measures from each fact table side by side within the same pivot. This technique is often used with budgets and actuals, and is discussed in Rob and Avi’s book, “Power Pivot and Power BI” in Chapter 18, starting on page 152.
Thanks for the quick response Tom!
I understand what you are saying and I actually have that book!
The only question I have now is that my budget table won’t update at all throughout the year so it’s static. Those are the budgeted amounts for each day of the week so it won’t really be a fact table because i’m not adding any new rows to it.
Sorry for the confusion, Tom. What I meant to ask is how could I take my budget table that is short and wide and convert that into a long and narrow budget table?
Please see example lookup table in attached workbook. If your transaction table is aggregated to location, cost center and date (and you have a field equivalent to “combo” in the transaction table), you could use such a table either as a lookup or use it join with you transaction data to revenue and expected revenue were on the same row.
Hi Tom,
I tried this suggestion and was able to create a custom column in my transaction table by doing the following:
Number.ToText([Cost Center ID) & “#” & Text.From(Cost Center) & “|” & Date.ToText(Post Date, “ddd”)
The column just about matches yours in the example except that the day of week abbreviation is not in all uppercase letters. Will that affect the lookup?
Hi Tom,
Please see my data model that I’ve attached. I was able to create the relationship between the Baseline Revenue Table and my fact table using the ‘Key’ column. But now I’m having troubles displaying the budgeted against the actual.
In the example workbook, the Key column is calculated differently between table ‘BWF FY17 Revenue’ and table ‘BaselineRevenue’: there is an extra space between # and BWF in the Key column for ‘BWF FY17 Revenue’ that is not . So when evaluating the model, I created an alternate Key calculated column (which did not have the extra space) and changed the relationship accordingly.
Generally speaking, differences in upper case vs lower case are ignored by Power Pivot.
Also I believe a more correct formula for Baseline Revenue is:
Baseline Rev:=SUMX ( ‘BWF FY17 Revenue’, RELATED ( BaselineRevenue[DailyExpectedRevenue] ) )
so you could create a measure like
Posted Rev – Baseline Rev:=[Total Posted Revenue] – [Baseline Revenue]
Please see attached workbook.
Note: The original Baseline Revenue formula would only give you the sum of DailyExpectedRevenue within the BaselineRevenue table, but I believe what is needed is the sum of DailyExpectedRevenue for each related row in ‘BWF FY17 Revenue’.
Hi Tom,
I wanted to pick back up on this thread because i’m encountering a similar issue.
I have my transactions table and am trying to sum all of the rows in the related baseline table so that I can then write a measure to show Actual-Budgeted. I created the relationship between the two tables using the “Key” column and my Baseline measure is returning “blank”
The measure is written as: Baseline:=SUMX(ImportRadiologyRevenue, RELATED(BaselineRevenue[DailyExpectedRevenue])
Do you have any thoughts? I checked to make sure I was creating the relationship starting from my transactions table.
The forum ‘Power Pivot’ is closed to new topics and replies.