Forum Discussion

gianmarco's avatar
gianmarco
Icon for Helper IV rankHelper IV
5 years ago
Solved

Need help with DAX formula

Dear All,

I have the following database:

 

from which I got the following table:

 

The measures involved are the followings:

1) COUNTROWS = COUNTROWS('Table')

2) DISTINCTCOUNT = DISTINCTCOUNT('Table'[RowNum])

3) QUANTITY = IF([COUNTROWS]<>[DISTINCTCOUNT],SUMX('Table','Table'[Q.ty]/[COUNTROWS]),SUMX('Table','Table'[Q.ty]))

 

The latter measure isn't working properly. If you look at the product "Kiwi" in the visualization, the quantity is "2", but it should be "1" according to the IF statement...


Is there a way to solve this? I attach a sample


Thank You

 

 

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey gianmarco ,

     

    that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.

    The following measure should work:

    Quantity NEW =
VAR vSummTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[ProdName],
            'Table'[Q.ty],
            'Table'[RowNum]
        ),
        "@Countrows", [COUNTROWS],
        "@Distinctcount", [DISTINCTCOUNT]
    )
VAR vSummTableQty =
    ADDCOLUMNS(
        vSummTable,
        "@Quantity",
            IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
    )
RETURN
    SUMX(
        vSummTableQty,
        [@Quantity]
    )

     

    And I think the result is how you want it:

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey gianmarco ,

     

    acutally the measure is working properly. I just think you want something else.

    SUMX is an iterator, what means it goes through the table line by line, calculates the expression for each line and at the end is building a SUM of the expressions for each line.

    For the kiwi, it takes the first line and calculates [Q.ty] (what is 1) divided by [COUNTROWS] (what is 1). So the result is 1.

    Then it will do the same for the next row, the result is again 1.

    Afterwards SUMX will sum up the two results and as 1 + 1 = 2 you get the correct result.

     

    I think what you want is:

    SUM( 'Table'[Q.ty]) / [COUNTROWS]

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    5 years ago

    Hi gianmarco ,

     

    I believe this is due to the fact that you've used SUMX. SUMX is an iterator function which will sum the result of each row evaluation.

     

    In your example, it does the following for Kiwi:

    Table Row 2: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1

    Table Row 3: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1

    SUMX does 1 + 1 = 2

     

    Try this measure instead:

     

    QUANTITY = 
IF(
    [COUNTROWS] <> [DISTINCTCOUNT],
    DIVIDE(SUM('Table'[Q.ty]), [COUNTROWS], 0),
    SUM('Table'[Q.ty])
)

     

     

    Pete

    • gianmarco's avatar
      gianmarco
      Icon for Helper IV rankHelper IV
      5 years ago

      Dear

      amitchandak 

      BA_Pete 

      selimovd 

       

      Thank you a lot for your kind answers. All three are great but still can't get what I really need.

      Thanks to you I could do the following:

      My problem now is the Total QUANTITY. I understand the 1,67 but I need it to be 4,00.

      Now you may ask what I need to get as a result... The fact is that I have some duplication/triplication of rows in my db because some products have different expiry dates. So I need to to maintain those rows but I also need NOT to sum the quantity of those rows... 😕

       

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        Hey gianmarco ,

         

        that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.

        The following measure should work:

        Quantity NEW =
VAR vSummTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[ProdName],
            'Table'[Q.ty],
            'Table'[RowNum]
        ),
        "@Countrows", [COUNTROWS],
        "@Distinctcount", [DISTINCTCOUNT]
    )
VAR vSummTableQty =
    ADDCOLUMNS(
        vSummTable,
        "@Quantity",
            IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
    )
RETURN
    SUMX(
        vSummTableQty,
        [@Quantity]
    )

         

        And I think the result is how you want it:

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis