Sum of unique values for a set of columns

[nicplancho]

Hi guys,

I'm a bit stuck with a calculation...

As you can see on the table attached, I get two lines for the same entry which leads to many duplicates in a basic sum and thus I would need to get the unicity of the ensemble {Date, Team, Project Category, Project Type} to be able to kill those duplicates.

I believe the answer should come from SUMX(SELECTCOLUMNS()) or SUMX(SUMMARIZE()) but the syntaxes have evolved lately and I don't really manage to get those functions work.

Thanks in advance for your help !

DateFile.NameDepartmentProjectCategoryProjectTypeValues

01/08/2022	FileName_082022.xlsb	Team A	category1	ProjectType1	0.7
01/08/2022	FileName_072022.xlsb	Team A	category1	ProjectType1	0.7
01/08/2022	FileName_072022.xlsb	Team B	category1	ProjectType1	0.2
01/09/2022	FileName_072022.xlsb	Team B	category1	ProjectType2	0.9
01/09/2022	FileName_082022.xlsb	Team B	category1	ProjectType2	0.9
01/09/2022	FileName_082022.xlsb	Team C	category2	ProjectType3	0.5
01/07/2022	FileName_072022.xlsb	Team C	category1	ProjectType2	0.1
01/07/2022	FileName_082022.xlsb	Team C	category1	ProjectType2	0.1

[nicplancho]

I updated my original message by adding more rows so that you may better understand.

As you can see in this table, for the ensemble {Date, Team, Project Category, Project Type}, we get duplicates and those duplicates lead to a wrong sum when I try to sum those results, thus my whole target is to manage to implement a sum based on the unicity of the ensemble {Date, Team, Project Category, Project Type}. Is this clearer now ?

I have managed to do this by using SumDistinctActuals = SUMX(DISTINCT(SELECTCOLUMNS(Table1,"Date, Team, Project Category, Project Type", [Date]&", "&[Team]&", "&[ProjectCategory]&", "&[ProjectType])), CALCULATE(MAX(Table1[Values]))).

This formula works when I pick only 1 date, 1 team, 1 project category and 1 project type.

As soon as I pick more through a slicer, the calculation is wrong again unfortunately. Any idea how I could get around this issue please ?

Thanks in advance for your support,

Nicolas.

[v-rongtiep-msft]

Hi @nicplancho ,

What is your desired output? Although you use a lot of words to describe your thoughts, I still don't understand them.

Could you please provide more details with your desired output and pbix file without privacy inforamtion?

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

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

[nicplancho]

I managed to get things work for 1 value at a time with the following formula SumDistinctActuals = SUMX(DISTINCT(SELECTCOLUMNS(Table1,"Date, Team, Project Category, Project Type", [Date]&", "&[Team]&", "&[ProjectCategory]&", "&[ProjectType])), CALCULATE(MAX(Table1[Values]))).

As said it works when I pick only 1 team, 1 project and 1 date but as soon as I select several values in my slicer (i.e Team A and Team B or Project A and Project B) then the results are completely wrong.

I believe it's linked with the fact that the operation MAX is getting confused by the filters' modification.

Would you have any clue about how to work around this issue please ?

Thanks in advance,
Nicolas.