cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SBC
Helper I
Helper I

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

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:

SBC_0-1664432812592.png

 

Thanks,

SBC

 

1 ACCEPTED SOLUTION

@SBC 

Please refer to attched updated file.

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @SBC 
Please refer to attached sample file withthe solution

1.png

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

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.

SBC_0-1664449807510.png

 

Output we got by executing your solution

SBC_2-1664449855706.png

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

 

Thanks,

SBC

 

 

 

 

 

 

@SBC 

Please refer to attched updated file.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors