Forum Discussion
Matrix Report - Column Difference
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?
7 Replies
Does not sound too hard, but you really need to include some samples of the tables you are using, and their relationships.
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 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)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).
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)
