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

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors