Forum Discussion

lkcz04185's avatar
lkcz04185
New Member
2 years ago

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 tw...
lkcz04185's avatar
lkcz04185
New Member
2 years ago

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

sjoerdvn's avatar
sjoerdvn
Solution Sage
2 years ago

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)
  • lkcz04185's avatar
    lkcz04185
    New Member
    2 years ago

    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.

    • sjoerdvn's avatar
      sjoerdvn
      Solution Sage
      2 years ago

      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.