Simulate Sales/Cost Prices with specific number or percentage

[KasperJ90]

Hi

I have a current layout in Power Query loaded to Excel with slicers as filters (I don't use PowerBi yet).
The current data in Power Query look as below:

Item Number | Item Sales Price | Item Cost Price

I want an option in my Excel file to increase/decrease the sales or cost price with a specific number or percentage.
Preferably like this:

Item Number | Item Sales Price | Item Cost Price | Item Sales Price Simulated | Item Cost Price Simulated

The idea is that it should be easy to simulate prices without changing the original source of the data, but only a visual in Excel.

Can you help me? Thank you

[Ehren]

PQ only refreshes its results when you trigger a refresh. If you want the updates to be instantaneous, you would need to define your simulated columns in Excel, by referencing the loaded PQ data and multiplying the simulation percentage.

[KasperJ90]

Thank you - can you guide me here on how to do it?

[Ehren]

That's more of an Excel quesiton than a PQ one, but here you go:

• In a cell to the right of the loaded PQ data, type:

= <click on the PQ Sales Price value in the same row> * (1 + <click on the percentage cell>)

• Modify the percentage cell reference so that it's absolute rather than relative. For example, if it's D2, make it $D$2.
• Copy and paste this cell formula into the rows below.

You should end up with something like this:

[KasperJ90]

Thank you - but I wanted to include it in my pivottable. The reason is that many people use different filters etc. so the size of the table changes a lot. If it is not possible to automatically refresh the pivottable when changing the cell with a new percentage I will use the first method you showed that requires a refresh.

[KNP · Accepted solution]

Hi @KasperJ90,

Rather than adding a column to every row of your data for the simulated increase. The more appropriate way to do it would be to use Power Pivot to create a measure. It has the advantage of working irrespective of the grouping used in the pivot table and reduces the model size by not adding unnecessary columns. 

So still following the steps provided by @Ehren but stopping short of creating the column in your main table.

See the gif below and attached file for reference.

Unfortunately, it doesn't solve the issue of needing to manually refresh at least one of the queries.

If you have the option to use a macro enabled workbook then I would recommend watching this video (https://www.youtube.com/watch?v=nVoqWGIN7Mc&ab_channel=ExcelIsFun) as a way of simplifying the process for the consumers.

I hope this helps.

[Ehren]

Ok. Here's how to do that:

• In Excel, select the cell containing the percentage.
• Click Formulas -> Define Name, and name the cell (for example, SalesPriceSimPct).
• With the cell still selected, navigate to the PQ area of the Data tab and click "From Table/Range" or "From Sheet" (the name of the button has changed over time).
• You should now see the % value in PQ, inside a column called "Column1".
• Right-click on the % value and select Drill Down
• You'll now just have the number.
• Look at the name of the query (and rename it if you like). Let's pretend it's called SalesPriceSimPct. You can now refer to this name from other queries. For example, in the query containing the Sales Price and Renenue, you can add a custom column called "Sales Price Simulation" using the formula = [Sales Price] * SalesPriceSimPct .

[KasperJ90]

Hi Ehren

Thank you!

I followed your guideline and I can set it up - but how can I get the data to automatically refresh when I change the percentage in the cell (defined as SalesPriceSimPct)? If I could change the percentage from 5% to 10% and then my tables change it would be amazing.

[Ehren]

Are you wanting to adjust all the rows by the same percentage?

If so, you could look into either using PQ Parameters, or pulling the percentage from a cell in your workbook and applying it via Add Custom Column.

[KasperJ90]

Hi Ehren
Thanks for your input. I want to calculate like below where I can enter a percentage (or number) that will change the sales price and revenue. See below. My data comes from an SQL database via Power Query so I can not just use cells for an calculation as in my examples. How can I set it up?