topic Re: DAX Syntax to calculate the delta of values between rows by another column in DAX Commands and Tips

DAX Syntax to calculate the delta of values between rows by another column

Anonymous — Thu, 27 Apr 2023 15:21:43 GMT

New to DAX. I have a single values column (Units) and another column with Scenario. . I am trying to create a DAX calc that subtracts Scenario 1 from Scenario 2 on each line item.

Here's my data table below as well as a screenshot of the pivot table view I'm trying to recreate (Pivot Table View), specifically the Deltas section, but would like to leverage DAX. Currently, I'm using power query to create calculated columns for the Deltas to create a new scenario called Deltas, then stitching it as a third scenario. This is resource intensive when done over many columns/rows.

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

Re: DAX Syntax to calculate the delta of values between rows by another column

lbendlin — Sat, 29 Apr 2023 22:13:14 GMT

I am trying to create a DAX calc that subtracts Scenario 1 from Scenario 2 on each line item.

Please define what you mean by "line item". Power BI aggregates everything automatically, and the only way to prevent that is to have unique values in each line - which you don't have.

Other than that you can use filters to calculate the required measure

Re: DAX Syntax to calculate the delta of values between rows by another column

Anonymous — Sun, 30 Apr 2023 03:39:00 GMT

Appreciate the response! I'm actually working within Excel's data model and trying to recreate the view with DAX in the linked image below which shows the unit delta between scenarios. (and additionally could add Revenue, Price deltas etc as well). Pivot Table View

The view you show in Power BI is close, except difficult to read with the Deltas being shown as a column and duplicated, vs being shown as third row beneath Scenario 2.

Re: DAX Syntax to calculate the delta of values between rows by another column

lbendlin — Sun, 30 Apr 2023 16:54:39 GMT

Your desired outcome is a little contrary to Power BI's philosophy. You might be better off if you continue to use Excel for that.