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.
I have the following measure:
Cases to Target Actual = (DOS Target - MIN DOS) * Max Daily Forecast
DOS Target is a parameter
MIN DOS is a measure
Max Daily Forecast is a measure
In the top table, which looks at item/DC combination, I want the expected results as shown, including the negative numbers (note the numbers are a bit off due to rounding). However, in the bottom table, which looks at item only, I want to ignore the negatives and see a total of the positive numbers only.
In this example, I would want the bottom to show 4.81 only (3.62+1.19).
I tried the following IF statement:
Is anyone able to help me with a measure that calculates this properly? Thanks!
Solved! Go to Solution.
Hi @maj,
You can try measure as:
Measure 2 =
IF(
HASONEVALUE('DOS Target'[DOS Target Value] ),
IF(('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)] < 0, 0, ('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]),
IF(
('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]>0,
SUM(TOTAL),
0
)
You can refer this:
https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Try this..this worked for me.
Hi @maj,
You can try measure as:
Measure 2 =
IF(
HASONEVALUE('DOS Target'[DOS Target Value] ),
IF(('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)] < 0, 0, ('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]),
IF(
('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]>0,
SUM(TOTAL),
0
)
You can refer this:
https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@maj Can't you just use the Filter pane and specify "Cases to Target Actual" is greater than 0?
No, because if the summed total is a negative number, it would get filtered out. Consider the following example:
These would be the results in the top table (item/DC combination):
5
-10
2
-3
In the second table (item only), the option you suggested would filter out this item. I really want it to return a value of 7.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |