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.
Dear all,
I've got lost a bit in all types of filters and transitions of Power BI and would appretiate a lot your help!
In my dataset I have columns for Stock and Orders in items that should be delivered. The objective is to create a measure that will evaluate how many items I can deliver, so the logic is the following: Match = IF (Stock>Orders) then Orders else Stock (example: I have 10 items of product A and orders for 15 items, so Match = 10 (statement IF (10>15) is FALSE).
This calculation should be done on row level, therefore I've created the following Base Measure to calcualte Orders and Stock:
Stock Qty =
SUMX(
Stock,
Stock[Stock Qty]
)
Open Orders =
SUMX(
'Open Orders',
'Open Orders'[Quantity]
)
As well as the formula for Match Measure:
Match (Stock) =
VAR _NetStock = [Stock Qty] - [Open Orders]
VAR _Result =
IF(
_NetStock >= 0,
[Open Orders],
[Stock Qty]
)
RETURN _Result
It works well on the lowest level of the granularity, but fails on aggregations. I expect 20 as the result for Herarchy Level 3, instead the result is 27.
Any ideas what do I miss?
Thank you very much!
Solved! Go to Solution.
@Sergii24
Not clear about the model and the relationship. Try an approach like below
Match (Stock) =
SUMX(
VALUES(Stock[Items])
VAR _NetStock = [Stock Qty] - [Open Orders]
VAR _Result =
IF(
_NetStock >= 0,
[Open Orders],
[Stock Qty]
)
RETURN _Result
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Sergii24
Not clear about the model and the relationship. Try an approach like below
Match (Stock) =
SUMX(
VALUES(Stock[Items])
VAR _NetStock = [Stock Qty] - [Open Orders]
VAR _Result =
IF(
_NetStock >= 0,
[Open Orders],
[Stock Qty]
)
RETURN _Result
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy , thank you very much! It looks like that outer SUMX is what was missing!
In my understanding once I wrote 2 separate measures for "Stock Qty" and "Open Orders" I was ready to use them together in a new, 3rd measure, having already a row context, which is wrong.
The correct way, instead, is to have another outer SUMX even in my third variable ("Match Stock") that is introducing row context for this new measure for the first time!
Thanks a lot for filling this gap in my knowledge! 🙂
Can you please provide a sample dataset or PBIX file? Thanks!
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |