Sum distinct

Izham
Izham Member
in Beast Mode

Hi, i have dataset of products that have repetition item code and quantity, how do i sum the quantity?

Best Answers

  • ggenovese
    ggenovese Contributor
    Answer ✓

    Using the example data you provided I am getting a deduped SUM of 1,600 using the beast mode I provided earlier, if I add a total row it also evaluates to 1,600. Not sure why it isn't working for you.

    Going back to my earlier question, I'm curious what your DAX calculation would evaluate to if you were to add a Product D that has a quantity of 300, 500, or 800?

    image.png

  • ggenovese
    ggenovese Contributor
    Answer ✓

    Hi - I shared a beast mode that you can use in a card, not an ETL

Answers

  • ggenovese
    ggenovese Contributor

    There are a number of ways to handle this, but the easiest might be to simply use MAX or AVG rather than SUM

  • ColemenWilson
    ColemenWilson Coach

    Using fixed functions! https://domo-support.domo.com/s/article/4408174643607?language=en_US

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach
    edited October 10 
    SUM(`quantity` / SUM(1) FIXED (BY `product_id`))

    Use a fixed function to determing the number of records you have for each product, divide the quantity by that to get fractional quantity and then SUM it all together to get the overall quantity.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Izham
    Izham Member
    image.png
    image.png

    Thank you for the feedback. However, when I compare it to an excel file, I still do not get the same answer.

    For the record, the duplication occurs annually. Please help.

  • ggenovese
    ggenovese Contributor

    Assuming your data looks kind of like this, where the same product qty is repeated within a year

    image.png

    then this beast mode will dedupe and sum by product and year: 

    SUM(MAX(`qty`) FIXED (by `product`,`year`))
    image.png

  • Izham
    Izham Member
    image.png

    I am still receiving different quantities, the correct amount shown in the Excel picture above...

  • ggenovese
    ggenovese Contributor

    Hi - when an item code is repeated, is the qty always the same corresponding value?

  • Izham
    Izham Member

    Yes, but it can be different items as well…😓

  • Izham
    Izham Member

    in excel I'm using this DAX code :

    SUMX(DISTINCT('Master Data'[LR_YR_art]),CALCULATE(DISTINCT('Master Data'[Soh CDC Current QTY])))

  • ggenovese
    ggenovese Contributor

    The DISTINCT expression in your DAX code is going to return a deduped list of qty values correct? Let's say for example, two items A & B both have a qty of 5, wouldn't the DAX code give you a total of 5 rather than 10? I don't know DAX so I want to make sure I'm interpreting that correctly.

  • Izham
    Izham Member

    Two distinct items will be used to calculate both items A and B. It will, of course, display two distinct totals.

    Here is the example (in simple excel) :

    Year

    Product

    Quantity

    2022

    a

    300

    2022

    a

    300

    2022

    b

    500

    2022

    c

    800

    2022

    a

    300

    Total

    2200

    Total year 2022

    : 1600

    not 2200 because item "a" is repeating 3 times

  • ggenovese
    ggenovese Contributor
    Answer ✓

    Using the example data you provided I am getting a deduped SUM of 1,600 using the beast mode I provided earlier, if I add a total row it also evaluates to 1,600. Not sure why it isn't working for you.

    Going back to my earlier question, I'm curious what your DAX calculation would evaluate to if you were to add a Product D that has a quantity of 300, 500, or 800?

    image.png

  • Izham
    Izham Member

    Is this in beast mode or in ETL?

  • ggenovese
    ggenovese Contributor
    Answer ✓

    Hi - I shared a beast mode that you can use in a card, not an ETL

  • Izham
    Izham Member

    Ok. Done it. Great!. Thank you very much yeahhhh 😁

Categories