Avoid duplicates doing the calculations

[dofrancis3]

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

[AilleryO]

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.

[dofrancis3]

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_l... 

[AilleryO]

Hi @dofrancis3 

I cannot access your file.

[Anonymous]

Hi @dofrancis3 

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

Best Regards,

Jayleny

[AilleryO]

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

[vivek31]

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

[sanalytics]

@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

[dofrancis3]

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:

[powerbidev123]

@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

[dofrancis3]

@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.

[powerbidev123]

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.

[dofrancis3]

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

So, how can i modifie the DAX.