Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |