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,
i've maked a table with several measures. Specifically the measure "afabricar" don't want that show negatives values (if the result it's negative the valued must be zero).
Well, if the measure show negatives values, the "Total" it's Ok, but if I make that the result show zero, the "Total" it's the same, don't changed.
Case A
afabricar =
IF(SUM(Productos[smincorregido])=0;[sminnew];SUM(Productos[smincorregido]))
-[stockactual]
+IF([totalconsumos]<[sminnew];[sminnew];[totalconsumos])
-'ops+stocks+cargas'[opspdts]
Case B:
afabricar = if(
(IF(SUM(Productos[smincorregido])=0;[sminnew];SUM(Productos[smincorregido]))
-[stockactual]
+IF([totalconsumos]<[sminnew];[sminnew];[totalconsumos])
-'ops+stocks+cargas'[opspdts])<0;
0;
IF(SUM(Productos[smincorregido])=0;[sminnew];SUM(Productos[smincorregido]))
-[stockactual]
+IF([totalconsumos]<[sminnew];[sminnew];[totalconsumos])
-'ops+stocks+cargas'[opspdts]
)
What's the problem?????
thank you.
Solved! Go to Solution.
Hi @BLM,
Try this formula, please. If it still doesn't work, please share your file or a dummy sample.
afabricar = VAR temp = IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] RETURN IF ( HASONEVALUE ( 'TableName'[codnombre] ); IF ( temp < 0; 0; temp ); SUMX ( SUMMARIZE ( 'TableName'; 'TableName'[codnombre]; "temp1"; IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] ); [temp1] ) )
Best Regards,
Dale
I understand your measure [afabricar] returns negative values as '0', and positive values stay the same, right?
Try using:
Correct totals for afabricar = SUMX(SUMMARIZE('tableforcodnombre', 'tableforcodnombre'[codnombre]), [afabricar])
(where 'tableforcodnombre' is obviously the lookup table where your 'codnombre' column is; if you don't have a lookup table for codnombre, you'll have to wrap it in VALUES)
Proud to be a Super User!
Paul on Linkedin.
Hi @BLM,
The cause is the "Total" has its own context, which doesn't add up the values of the other rows. You try out this formula. If you want a accurate formula, please share your file or a dummy sample.
afabricar = VAR temp = IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] RETURN IF ( HASONEVALUE ( 'TableName'[codnombre] ); temp; SUMX ( SUMMARIZE ( 'TableName'; 'TableName'[codnombre]; "temp1"; IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] ); [temp1] ) )
Best Regards,
Dale
I'm tryed out your formula, and the result it's the same. Show the negatives values and the "Total" it's Ok, but the question is that don't show the negatives values but 0.
Hi @BLM,
Could you please mark the proper answer as solution?
Best Regards!
Dale
Hi @BLM,
Try this formula, please. If it still doesn't work, please share your file or a dummy sample.
afabricar = VAR temp = IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] RETURN IF ( HASONEVALUE ( 'TableName'[codnombre] ); IF ( temp < 0; 0; temp ); SUMX ( SUMMARIZE ( 'TableName'; 'TableName'[codnombre]; "temp1"; IF ( SUM ( Productos[smincorregido] ) = 0; [sminnew]; SUM ( Productos[smincorregido] ) ) - [stockactual] + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] ) - 'ops+stocks+cargas'[opspdts] ); [temp1] ) )
Best Regards,
Dale
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |