topic Re: Avoid duplicates doing the calculations in DAX Commands and Tips

Avoid duplicates doing the calculations

dofrancis3 — Thu, 13 Feb 2025 08:09:25 GMT

Dear Colleagues,

I would like to request your assistance please !

As you can see in the table below, I have duplicates. How can I proceed to make the calculation (sum) without considering the duplicates?

Thank you for your support

Re: Avoid duplicates doing the calculations

powerbidev123 — Thu, 13 Feb 2025 08:21:01 GMT

Hi dofrancis3 ,

If the duplicates are not identical across all columns but need to be grouped by Country, Year, and Quarter, use DAX:

SUM_NO_DUPLICATES = SUMX( DISTINCT(SELECTCOLUMNS('Table', "Country", 'Table'[Country], "Year", 'Table'[Year], "Quarter", 'Table'[Quarter], "P1", 'Table'[P1], "P2", 'Table'[P2])), 'Table'[P1] + 'Table'[P2] )

This formula ensures that only unique (Country, Year, Quarter, P1, P2) combinations are considered.

Re: Avoid duplicates doing the calculations

dofrancis3 — Thu, 13 Feb 2025 08:36:51 GMT

Dear powerbidev123 in my case the duplicates are identical across all columns.

So, how can i modifie the DAX.

Re: Avoid duplicates doing the calculations

sanalytics — Thu, 13 Feb 2025 08:58:42 GMT

dofrancis3

Please provide your expected output. If your data model consist mutiple duplicate column then suggesting you provide the data based on that..
Regards

sanalytics

Re: Avoid duplicates doing the calculations

vivek31 — Thu, 13 Feb 2025 09:32:21 GMT

HI dofrancis3,

you can use this type of formla to Avoid duplicates doing the calculations

P1 Total = SUMX(DISTINCT('Table (5)'),'Table (5)'[P1])

P2 Total = SUMX(DISTINCT('Table (5)'),'Table (5)'[P2])

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you

Re: Avoid duplicates doing the calculations

AilleryO — Thu, 13 Feb 2025 09:42:02 GMT

Hi,

Especially if all your columns are identical, I think the best options is not to go into complicated DAX but just to remove duplicates using Power Query.

In case using Power Query is not an option, for example you're working with a Direct Query, Semantic model...
1/ You should consider why duplicates are there... since it is not "normal" to have duplicates in the source. So, is it possible to clean the source ?
2/ If not possible to clean the source... Why not building a table with DAX (CALCULATETABLE...) without the duplicates (VALUES, DISTINCT...) and then use this table to do your calculation with simple SUM ?

As far as I am concerned I do not like to work with a source with duplicates, it might be the source of so many troubles, so the best option, would be to clean the source at whatever level (source itself, Power Query, DAX) and then work with clean data.

Hope it helps and it is only my wiew of a situation I do not deeply know

Re: Avoid duplicates doing the calculations

dofrancis3 — Thu, 13 Feb 2025 12:19:29 GMT

Dear sanalytics, AilleryO , vivek31 , powerbidev123 ;

As per the table below I would like to calculate the Sum of each column by COUNTRY without duplication

Please see below example of output expected:

Re: Avoid duplicates doing the calculations

powerbidev123 — Thu, 13 Feb 2025 12:45:54 GMT

dofrancis3 , try using this for Burkina Faso value column:P1
P1 =
SUMX(
SUMMARIZE(
FILTER( TableName, TableName[Country] = "Burkina Faso" ),
TableName[Year], TableName[Quarter], "UniqueP1", MAX(TableName[P1])
),
[UniqueP1]
)

If it works then use the same for other P2,z1,z2,m1,m2 columns as well

Re: Avoid duplicates doing the calculations

dofrancis3 — Thu, 13 Feb 2025 13:02:44 GMT

powerbidev123 sorry it doen't work please just to let you know that Burkina foso is just an exemple of out kind of output i expect so i don't need to write Burkina faso in my DAX mesure because duplication can be for other countries.

Re: Avoid duplicates doing the calculations

AilleryO — Thu, 13 Feb 2025 15:25:51 GMT

Hi dofrancis3 ,
Sorry I just saw your screen copy.

First you should unpivot all your columns except the country, year and quarter.

So you'll get all values in one column and one column which states if it is P1 or P2...

This column will be our filter in the visual (rows or column).

Then you remove the duplicates, (with dedicated button after selecting all your columns)

and finally you just have to sum up your values in a visual, maybe without any DAX at all.

Even though I would recommend a simple DAX Like : SUM( [Attribute] ).

Attribute being the name of your column with your values.

Then choose if you want the country in rows or columns,

and do the same with the column with values P1, P2.

If you provide sample data I can make you a quick pbix to show you.

Re: Avoid duplicates doing the calculations

dofrancis3 — Thu, 13 Feb 2025 18:04:36 GMT

Dear AilleryO ;

Kindly, find bellow sample data as requested

Thank you for support.

https://docs.google.com/spreadsheets/d/1WEI2yxmBLBqqG6k-PMSS8BtpySeMfj_xJlmK_DZ_et0/edit?usp=drive_link

Re: Avoid duplicates doing the calculations

Anonymous — Fri, 14 Feb 2025 03:04:11 GMT

Hi dofrancis3

Please grant access to your form so that I can open your link.

Best Regards,

Jayleny

Re: Avoid duplicates doing the calculations

AilleryO — Fri, 14 Feb 2025 09:01:47 GMT

Hi dofrancis3

I cannot access your file.