topic Re: Matrix Report - Column Difference in DAX Commands and Tips

Matrix Report - Column Difference

lkcz04185 — Fri, 08 Sep 2023 10:55:53 GMT

Hi,

working on a Power Bi matrix style report, like below.

Slicer to select two Dates (years,qts) to compare prices per product category for.

How do I write a measure to calculate deltas of the two price categories (low, high) related to the selected dates. So that, the formula dynamically reference the Dates selection to filter respective product prices.

Can somebody help, please?

Re: Matrix Report - Column Difference

sjoerdvn — Fri, 08 Sep 2023 12:24:51 GMT

Does not sound too hard, but you really need to include some samples of the tables you are using, and their relationships.

Re: Matrix Report - Column Difference

lkcz04185 — Fri, 08 Sep 2023 14:18:16 GMT

sjoerdvnthanks for the feeedback. Using just one table atm. There are 3 slicers configured. Date, Tower, Country.

I need to calculate deltas between Price1 and Price2 for each product and two selectable dates at a time.

Date	Tower	Country	Category	Product	Price1 Low	Price1 High	Price 2 Low	Price 2 High
2019-11-30	Security	USA	Network	Product 1	85.6	95.7	252.6	282.5
2019-11-30	Security	USA	Network	Product 2	363.9	396.7	1074.0	1170.8
2019-11-30	Security	USA	Network	Product 3	268.1	292.8	791.3	864.2
2019-11-30	Security	USA	Network	Product 4	3.6	4.0	10.6	11.8
2019-11-30	Security	USA	Application	Product 5	4.1	4.6	12.1	13.6
2019-11-30	Security	USA	Application	Product 6	1.2	1.3	3.5	3.8
2019-11-30	Security	USA	Application	Product 7	231.0	257.2	681.8	759.1
2019-11-30	Security	USA	Application	Product 8	269.1	298.3	794.2	880.4
2020-03-31	Security	USA	Network	Product 1	85.6	95.7	252.6	282.5
2020-03-31	Security	USA	Network	Product 2	363.9	396.7	1074.0	1170.8
2020-03-31	Security	USA	Network	Product 3	268.1	292.8	791.3	864.2
2020-03-31	Security	USA	Network	Product 4	3.6	4.0	10.6	11.8
2020-03-31	Security	USA	Application	Product 5	4.1	4.6	12.1	13.6
2020-03-31	Security	USA	Application	Product 6	1.2	1.3	3.5	3.8
2020-03-31	Security	USA	Application	Product 7	231.0	257.2	681.8	759.1
2020-03-31	Security	USA	Application	Product 8	269.1	298.3	794.2	880.4

Re: Matrix Report - Column Difference

sjoerdvn — Fri, 08 Sep 2023 14:34:50 GMT

so assuming the Date column has no relationship with a date dimension, I think it should be something like this:

delta price1 = VAR d1 = MIN('atm'[Date]) VAR d2 = MAX('atm'[Date]) RETURN CALCULATE(MIN('atm'[Price1],'atm'[Date]=d2) - CALCULATE(MIN('atm'[Price1],'atm'[Date]=d1)

Re: Matrix Report - Column Difference

lkcz04185 — Mon, 11 Sep 2023 09:07:18 GMT

Thank you for your reply!

A bit confused about the parenthesis in the formula. I've tried to add some, as well.

Either way I'm only getting this error.

"A single value for colum 'Price1' in table 'Current' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result".

I actually don't need a single value it should be a column with values for every row (Product).

Re: Matrix Report - Column Difference

sjoerdvn — Mon, 11 Sep 2023 12:20:22 GMT

you tried someything like this?

delta price1 = VAR d1 = MIN('atm'[Date]) VAR d2 = MAX('atm'[Date]) RETURN CALCULATE(MIN('atm'[Price1]),'atm'[Date]=d2) - CALCULATE(MIN('atm'[Price1]),'atm'[Date]=d1)

Re: Matrix Report - Column Difference

lkcz04185 — Tue, 12 Sep 2023 07:31:40 GMT

Hi, thanks for the reply.

I've adjusted the formula and added the measure to the Values pane with Prices. So, the Delta Measure calculation Columns have no errors, but probably don't reflect the Dates Slicer. I would need the delta columns at the end of the table to calculat dPrice and dPrice2 for 2023 vs. 2020. The dates to be selected dynamically. Not sure if this can work with Matrix type of report.

Appreciate if you can have one more look at it. Cheers.

Re: Matrix Report - Column Difference

sjoerdvn — Tue, 12 Sep 2023 12:48:05 GMT

It looks like you're also using the year of that date as a column in that grid. That means that the measure is in the context of that column. So you will only see deltas within one year.
So I suggest to get rid of that column. You could add measures to display the price at the start of the selection and the price at the end of the selection.