Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |