This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
New to DAX and trying to shift as many calculations/steps that I had been doing in Power Query to DAX and the Data Model in order to leverage relationships and reduce the refresh time. I am trying to create a DAX calc to recreate the Pivot Table view shown below, specifically the "Deltas" scenario. Currently, I have a separate power query that takes the data with Scenario 1/2s, unpivots, adds a "Delta" calculated column, repivots, creates a third scenario called "Deltas", then joins it back to the raw data table which I pivot off of. This adds a lot of time to the refresh when done over several metrics (units, revenue, GP$ etc etc). I'm wondering if I can use a DAX formula to accomplish this same thing. Below is a simplified view of my data set and a pivot table view of what I'm trying to recreate. I asked for help on the Power BI forums but no luck.
| Customer | Product | Scenario | Quarter | Units | Price |
|---|---|---|---|---|---|
| Customer A | Product A | Scenario 1 | CQ1-23 | 55 | $49.00 |
| Customer B | Product A | Scenario 1 | CQ1-23 | 81 | $77.00 |
| Customer C | Product B | Scenario 1 | CQ1-23 | 23 | $98.00 |
| Customer A | Product A | Scenario 1 | CQ2-23 | 98 | $62.00 |
| Customer B | Product A | Scenario 1 | CQ2-23 | 67 | $61.00 |
| Customer C | Product B | Scenario 1 | CQ2-23 | 78 | $8.00 |
| Customer A | Product A | Scenario 2 | CQ1-23 | 38 | $45.00 |
| Customer B | Product A | Scenario 2 | CQ1-23 | 73 | $79.00 |
| Customer C | Product B | Scenario 2 | CQ1-23 | 27 | $22.00 |
| Customer A | Product A | Scenario 2 | CQ2-23 | 79 | $90.00 |
| Customer B | Product A | Scenario 2 | CQ2-23 | 39 | $74.00 |
| Customer C | Product B | Scenario 2 | CQ2-23 | 5 | $79.00 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
If you are OK with the revised structure which i am proposing in the image below, then you will not have to use the Query Editor - in columns K and N, we will calculate the deltas. Would you be OK with this. Also, in your image, what goes in the Value column (the first column)?
Excel 365 Pro Plus with Power Pivot and Power Query.
Visualize Delta data with PivotCharts and Slicer.
No formulas, no vba macro.
https://www.mediafire.com/file_premium/vil53t0a5c5juh4/05_01_23.xlsx/file
https://www.mediafire.com/file_premium/pn32g0le272p0ep/05_01_23.pdf/file
