Forum Discussion

EsterBR's avatar
EsterBR
Helper II
2 years ago
Solved

DAX function to SUM wothout duplicates

Hi,

 

I have a table and I had to unpivot some columns as a result some values have been duplicated in Sales Category. Is there any Dax function to avoin to sum sales category 3 times so when a I sum Sales category by category in a table sums 300 and not 900? Thank you so much

 

 

Need Help

4 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    Could you ignore the Sales Category Number and just use the Sum of Sales Category when you go to build your visual -

    this is assuming that the sub-category isn't missing any Sales figures.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi EsterBR ,

    If I understand correctly, the issue is that you want to sum Sales category without duplicates. Please try the following methods and check if they can solve your problem:

    1.Go to the Modeling tab and select New Measure.

     

    2.Create a measure using the sumx and distinct functions. Enter the following DAX formula.

    Sum Without Duplicates =
SUMX(
    DISTINCT('Table'[Sales Category]),
    CALCULATE(SUM('Table'[Sales Amount]))
)

     

    3.Using the measure in the report to sum the sales amount.

     

    Looking forward to your reply.

    Best Regards,

    Wisdom Wu

    • EsterBR's avatar
      EsterBR
      Helper II
      2 years ago

      Thanks,  but unfortunately it does not work: