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.
Hello everybody.
I have a simple calculation of [Inventory]-[Sales]. I need the result if it is negative otherwise, there should be a blank.
In the ItemMaster there are two levels: Area and Item.
My current approach is not working. It shows me no result on Area-level - as you can see:
Area | Item | Sales | Inventory | Inventory-Sales |
A1 | 1 | 44457,18 | 12500 | -31957,18 |
2 | 215000 | |||
3 | 486829,7115 | 315000 | -171829,712 | |
Result | 531286,8915 | 542500 |
|
The function is:
=IF(SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales])<0;SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales]);BLANK())
Does someone know where my mistake is?
Hi @joshua1990
The filter context on the Result line is the whole table. Totals are not the sum of the indivudal lines, but are calculated according the filter context.
So if you make sure that your filter context only filters the negative values then the result line will show that value.
I created two measures under the assumption that the values are in one record (otherwise you need to adjust the measures):
Inventory_Sales = SUM(ItemMaster[Inventory]) - Sum(ItemMaster[Sales])
Inventory_Sales Negatives Only = SUMX(
filter('ItemMaster',
'ItemMaster'[Inventory]<ItemMaster[Sales]),
[Inventory_Sales]
)
Hope this helps.
Hi @joshua1990
try to use calculate for correct row context usage and maybe SUM instead of SUMX
for example
Measure =
var Expression = CALCULATE(SUM('Item Master'[Inventory]))-CALCULATE(SUM(Sales[Sales]))
RETURN
IF (Expression < 0;Expression; BLANK())
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi Joshua1990
Can you just clarify what you mean regarding your two levels, I don't understand, do you mean there are two types of record in the data table? Could you include a screen shot of the data?
Are you expecting a value in the Inventory-Sales column against the Result row? It's blank because it's not a negative number.
Hey @cpearson Thanks for your support!
I just mean there are two granularities like this:
Area | Item |
A | A1 |
A | A2 |
B | B1 |
I would expect the sum of both negative values 🙂
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 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |