Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • shebr's avatar
    shebr
    6 years ago
    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?

shebr's avatar
shebr
Resolver III
6 years ago

Hi Anonymous 

 

I have the exact expected result as you have stated. Same values. 

 

I dont understand what further you need? Can you clarify what the measure is intended to do? At the moment, from what you have posted, you want to add the following

 

1) The SUM of QTY where CLASSIFICATION <> "C&I" AND FLAG = 1. With your data this is total of 300, correct?

you want to add this value to the following:

2) The SUM of QTY 2 where CLASSIFICATION = "C&I". With your data this is 1400.

 

Together this is 1700. As per your data.

 

What is the issue here?

 

Thanks

 

Shebr

Anonymous's avatar
Anonymous
Not applicable
6 years ago
SO IDCUSTOMERCLASSIFICATIONFLAGQTYQTY2
4C2EBO0400 

 

Suppose a row with classification = EBO and FLAG = 0, then the measure is considering this qty also. But as per the formula no need to consider, because flag not equal to 1.

  • shebr's avatar
    shebr
    Resolver III
    6 years ago

    Of course if different values appear in the data you need to handle this in the measure. Either you can add it as an additional variable (VAR) See my example, or you can add it in the FILTER formula.

     

    Does that make sense?

     

    If you can provide the FULL specs then I can give a complete answer, otherwise it will not be complete.

     

    Thanks

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Already restricted in the below formula. But still it is considering flag = 0 also.
    var one = CALCULATE(SUM(SO_BI_FACT[QTY]),
    FILTER(SO_BI_FACT,
    SO_BI_FACT[CLASSIFICATION] <> "C&I"
    && SO_BI_FACT[FLAG]=1 ))
  • shebr's avatar
    shebr
    Resolver III
    6 years ago

    If you see this image it is not including SO ID 4.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes, the formula is ok. But still adding that qty also.

    I dont know, why the formula is considering this record also.

  • shebr's avatar
    shebr
    Resolver III
    6 years ago

    Can you show me a screen shot of your data table? Do you have any filters applied? Are your data formats in the right format? Check if your 'Flag' is text or numeric. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Sorry bro, formula is fine. Source data is wrong.

  • shebr's avatar
    shebr
    Resolver III
    6 years ago

    No Problem, I thought I was going crazy.


    ** Did this answer your question, mark as resolved and hit the Kudos button!**