cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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:

Thanks,

SBC

1 ACCEPTED SOLUTION
Super User

Please refer to attched updated file.

3 REPLIES 3
Super User

Hi @SBC
Please refer to attached sample file withthe solution

``````Value =
VAR NormalValue = SUM ( 'Table'[salesvalues] )
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 (
NormalValue,
Difference * _Sign
)
RETURN
Result``````
Helper II

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.

Output we got by executing your solution

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

Thanks,

SBC

Super User

Please refer to attched updated file.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors
Users online (2,658)