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
Solved! Go to Solution.
Hi @SBC
Please refer to attached sample file withthe solution
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.
Output we got by executing your solution
MYL Data is missing.Please provide any alternate solution to resolve this issue,
Thanks,
SBC
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!