topic how to calculate difference between two rows in a table ? in Desktop

how to calculate difference between two rows in a table ?

shilunannan — Thu, 04 Mar 2021 20:20:24 GMT

Hi,

Any help is appreciated. I'm creating a dashboard to display pricing info. With selecting two price curves, the corresponding grades are displayed in the table beside it. I'm wondering if there is a way to show the difference between selected two grades?

And here is my sample table.

EOD Date	Grid Point Month	Price Curve	Price
3/1/2021	3/1/2021	C5-CRW	0.9
3/1/2021	4/1/2021	C5-CRW	1.5
3/1/2021	5/1/2021	C5-CRW	0.7
3/1/2021	6/1/2021	C5-CRW	0.2
3/1/2021	7/1/2021	C5-CRW	-1.5
3/1/2021	8/1/2021	C5-CRW	-1.6
3/1/2021	9/1/2021	C5-CRW	-1.7
3/1/2021	10/1/2021	C5-CRW	-1.2
3/1/2021	11/1/2021	C5-CRW	-1.2
3/1/2021	12/1/2021	C5-CRW	-1.2
3/1/2021	1/1/2022	C5-CRW	-1.75
3/1/2021	2/1/2022	C5-CRW	-1.75
3/1/2021	3/1/2022	C5-CRW	-1.75
3/1/2021	4/1/2022	C5-CRW	-2.75
3/1/2021	5/1/2022	C5-CRW	-2.75
3/1/2021	6/1/2022	C5-CRW	-2.75
3/1/2021	7/1/2022	C5-CRW	-4.1
3/1/2021	8/1/2022	C5-CRW	-4.1
3/1/2021	9/1/2022	C5-CRW	-4.1
3/1/2021	10/1/2022	C5-CRW	-3.6
3/1/2021	11/1/2022	C5-CRW	-3.6
3/1/2021	12/1/2022	C5-CRW	-3.6

Really appreciated!

Re: how to calculate difference between two rows in a table ?

amitchandak — Fri, 05 Mar 2021 02:58:30 GMT

shilunannan , You can have to create a measure like

new measure =
var _min = minx(allselected(Table), Table[Price Curve])
var _max = maxx(allselected(Table), Table[Price Curve])
return
calculate(Average(Table[Price]), filter(Table, Table[Price Curve] =_max)) -calculate(Average(Table[Price]), filter(Table, Table[Price Curve] =_min))

Re: how to calculate difference between two rows in a table ?

v-kelly-msft — Mon, 08 Mar 2021 07:03:14 GMT

Hi shilunannan ,

Create a measure as below:

Measure = IF(ISINSCOPE('Table'[Price Curve]), SUM('Table'[Price]), CALCULATE(SUM('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[Price Curve]=MAX('Table'[Price Curve])&&'Table'[EOD Date]=MAX('Table'[EOD Date])))-CALCULATE(SUM('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[Price Curve]=Min('Table'[Price Curve])&&'Table'[EOD Date]=MAX('Table'[EOD Date]))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!