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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors