Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SBC
Helper III
Helper III

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors