Forum Discussion
DAX HELP
Below formula giving wrong value. Please help INVOICED QTY = CALCULATE(SUM(SO_BI_FACT[QTY]), FILTER(SO_BI_FACT, SO_BI_FACT[CLASSIFICATION] <> "C&I" && SO_BI_FACT[FLAG]=1 )) + CALCULATE(S...
shebr wrote:Hi Anonymous
Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?
My Calc =var one = CALCULATE(SUM(SO_BI_FACT[QTY]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] <> "C&I"&& SO_BI_FACT[FLAG]=1 ))VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] = "C&I"))return CALCULATE(one + two, ALL(SO_BI_FACT))Anonymous did you try this measure?
Hi Anonymous
congrats 🙂
any details?
the most common error in your case - is not using ALL() inside filter. But it is the step in the dark from my side
INVOICED QTY =
CALCULATE(SUM(SO_BI_FACT[QTY]),
FILTER(ALL(SO_BI_FACT),
SO_BI_FACT[CLASSIFICATION] <> "C&I"
&& SO_BI_FACT[FLAG]=1 ))
+
CALCULATE(SUM(SO_BI_FACT[QTY2]),
ALL(SO_BI_FACT),
SO_BI_FACT[CLASSIFICATION] = "C&I")| SO ID | CLASSIFICATION | FLAG | QTY | QTY2 |
| 1 | C&I | 0 | 100 | |
| 2 | EBO | 1 | 200 | |
| 3 | EBO | 1 | 100 | |
| 4 | C&I | 1 | 500 | |
| 5 | C&I | 0 | 800 |
Hi Anonymous
Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?
My Calc =var one = CALCULATE(SUM(SO_BI_FACT[QTY]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] <> "C&I"&& SO_BI_FACT[FLAG]=1 ))VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] = "C&I"))return CALCULATE(one + two, ALL(SO_BI_FACT))SO ID CUSTOMER CLASSIFICATION FLAG QTY QTY2 1 C1 C&I 0 100 2 C2 EBO 1 200 3 C2 EBO 1 100 4 C1 C&I 1 500 5 C1 C&I 0 800 RESULT CUSTOMER FINAL QTY C1 1400 C2 300 Yep, this is the expected result with my last message:
shebr wrote:Hi Anonymous
Can you try this as a measure? Let me know how you get on? Are you expecting 1700 as your value?
My Calc =var one = CALCULATE(SUM(SO_BI_FACT[QTY]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] <> "C&I"&& SO_BI_FACT[FLAG]=1 ))VAR two = CALCULATE(SUM(SO_BI_FACT[QTY2]),FILTER(SO_BI_FACT,SO_BI_FACT[CLASSIFICATION] = "C&I"))return CALCULATE(one + two, ALL(SO_BI_FACT))Anonymous did you try this measure?