I have a data model for a customer metrics type Power BI report. There are tables for Sales (invoices to the customer), Inventory, and “Savings Projects”.
I’m currently adding some “cost savings” reporting, and the data needs to come from multiple sources.
“Savings Projects” are efforts we make to save the customer money, such as “replace this particular tool with a less expensive tool from a different vendor” . This project may have saved, say, 10k. The table has a date, a description, and an amount. We call these “Project Savings”.
Each Sales line also has some savings I need to capture: The invoice record (queried from our ERP system) includes the “benchmark” for each item, and comparing that benchmark to the unit price gives me a savings amount that we call “PPV Savings”.
There are several other potential sources for savings that will probably end up getting added to the model.
What I need to be able to do is grab some records from the Sales table and flag them as "PPV Savings’, some other records from the project table and flag them as “Project Savings”… and then from wherever else management can find to data mine.
If I need to I can requery everything from the sources, but it would rerun the Sales query, which is millions of rows and takes some time.
I’m currently working through the Advanced Transformations course, but wanted to make sure what i need is even possible before I invest all the hours.
Sounds intresting. Think the key is, what logic is defined for flagging those records you want to reuse. Can you elaborate on that and share a small mock up in Excel (with expected outcome) so we can get to query-ing
I’ve attached some sample data for the Customers table, the Sales table, and the Project Savings table.
(There’s also an item table, but that’s a layer of complexity I’ll figure out later.*)
I need a “Savings” table that has the fields:
Customer key, savings date, item number, savings type, savings source, savings amount
The source needs to reference tables that are already imported into power BI separately, and would essentially be:
*I do need to do some slicing/analysis by item categories, but item numbers aren’t necessarily tied to projects so I’ll make a dummy project item and assign it a suitable category.
I can write that as a sql query straight from the source(s), but it would then rerun the main sales query logic, and that’s an expensive query to run.Program Metrics Model.xlsx (117.1 KB)
Quick question 'cos your sample file didn’t include a desired outcome…
Do I understand correctly that you want to replace the Sales/Savings Type with a Savings Type ="“PPV Savings” and replace Project Savings/Savings Description with Savings Type ="“Project Savings” in the new Savings table?
Also no filtering needs to be applied on either table just join the data from the selected columns?
Oops, I think I worded my select statment incorrectly. I have two attributes of the savings to distinguish. There’s a savings type, which is really one of two values… “savings” or “avoidance”, and then a savings source, which is “PPV Savings”, or “Project Savings”, (and someday other sources).
All rows in the “project savings” table would have a savings type of “savings” so that could be hard coded in the select from that table, while the savings type is stored in a field in the sales table.
Let me correct my original pseudosql statement here:
