DAX function to SUM wothout duplicates

[EsterBR]

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

[v-jiewu-msft]

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]

Thanks,  but unfortunately it does not work:

[vicky_]

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.

[ryan_mayu · Accepted solution]

@EsterBR 

why not use max or min to only get 300?