cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors