Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lkcz04185
New Member

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?

PowerBIMatrixReport.png

 

 

7 REPLIES 7
sjoerdvn
Super User
Super User

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)

PriceMatrixDelta.png

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.

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.

lkcz04185
New Member

@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.

 

DateTowerCountryCategoryProductPrice1 LowPrice1 HighPrice 2 LowPrice 2 High
2019-11-30SecurityUSANetworkProduct 185.695.7252.6282.5
2019-11-30SecurityUSANetworkProduct 2363.9396.71074.01170.8
2019-11-30SecurityUSANetworkProduct 3268.1292.8791.3864.2
2019-11-30SecurityUSANetworkProduct 43.64.010.611.8
2019-11-30SecurityUSAApplicationProduct 54.14.612.113.6
2019-11-30SecurityUSAApplicationProduct 61.21.33.53.8
2019-11-30SecurityUSAApplicationProduct 7231.0257.2681.8759.1
2019-11-30SecurityUSAApplicationProduct 8269.1298.3794.2880.4
2020-03-31SecurityUSANetworkProduct 185.695.7252.6282.5
2020-03-31SecurityUSANetworkProduct 2363.9396.71074.01170.8
2020-03-31SecurityUSANetworkProduct 3268.1292.8791.3864.2
2020-03-31SecurityUSANetworkProduct 43.64.010.611.8
2020-03-31SecurityUSAApplicationProduct 54.14.612.113.6
2020-03-31SecurityUSAApplicationProduct 61.21.33.53.8
2020-03-31SecurityUSAApplicationProduct 7231.0257.2681.8759.1
2020-03-31SecurityUSAApplicationProduct 8269.1298.3794.2880.4

 

 

sjoerdvn
Super User
Super User

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.