topic How to calculate the difference between two rows of a single column in power BI in DAX Commands and Tips

How to calculate the difference between two rows of a single column in power BI

SBC — Thu, 29 Sep 2022 06:30:43 GMT

Hi,

How to calculate the difference between buy and sell of a salesvalues coulmn based on country by converting to rows to column of buy_sell column .

Input:

Country	category	buy_sell	salesvalues
India	A	buy	Null
India	A	sell	-75,500.00
USA	B	buy	-60,095
USA	B	sell	70,901
AUS	C	buy	29,923
AUS	C	sell	-3,260,307
AUS	D	buy	-60,750.00
AUS	D	sell	-1500
USA	E	buy	10
USA	E	sell	20
USA	F	buy	-20000
USA	F	sell	Null

Expected Output:

Thanks,

SBC

Re: How to calculate the difference between two rows of a single column in power BI

tamerj1 — Thu, 29 Sep 2022 07:30:29 GMT

Hi SBC
Please refer to attached sample file withthe solution

Value = VAR NormalValue = SUM ( 'Table'[salesvalues] ) VAR BuyValue = CALCULATE ( SUM ( 'Table'[salesvalues] ), 'Table'[buy_sell] = "buy" ) VAR SellValue = CALCULATE ( SUM ( 'Table'[salesvalues] ), 'Table'[buy_sell] = "sell" ) VAR Difference = ABS ( ABS ( SellValue ) - ABS ( BuyValue ) ) VAR _Sign = DIVIDE ( SellValue + BuyValue, ABS ( SellValue + BuyValue ) ) VAR Result = IF ( HASONEVALUE ( 'Table'[buy_sell] ), NormalValue, Difference * _Sign ) RETURN Result

Re: How to calculate the difference between two rows of a single column in power BI

SBC — Thu, 29 Sep 2022 11:13:20 GMT

Hi tamerj1 ,

Can we use table visual instead of Matrix visual , by using matrix visual we are missing data which consits of null values in it.

Output we got by executing your solution

MYL Data is missing.Please provide any alternate solution to resolve this issue,

Thanks,

SBC

Re: How to calculate the difference between two rows of a single column in power BI

tamerj1 — Thu, 29 Sep 2022 16:17:15 GMT

SBC

Please refer to attched updated file.