Forum Discussion
o59393
Post Prodigy
Post ProdigyHow to return unique column values with summarize with distinct
Hi all I created a table using summarize and distinct with intention to have unique combinations of the columns Tier 1 + Tier 2 and a percentage of 100%. However I am getting duplicate rows as...
I put this together, I "think" you want to do a summary and then a group by.
TableTest = var _tab = SUMMARIZECOLUMNS( Input[Tier 1: Process Facet], Input[Tier 2: Activity Facet], INPUT[Activity ID], "ID", MIN(INPUT[Activity ID]), "perC", MIN(INPUT[%]), "name", MIN(Input[Name]), "func", MIN(Input[Function]), "Area", MIN(Input[Area])//, ) RETURN GROUPBY(_tab, Input[Tier 1: Process Facet], Input[Tier 2: Activity Facet], "total", SUMX(CURRENTGROUP(), [perC] ))
Greg_Deckler
Community Champion
Community Championo59393 Well, the way you are summarizing it, there are three unique values, .15, .3 and .2 which is 20%. If you add Internal Area to your summarization, then you would get 23% but your total is then 135%. Ah, add Activity ID to your summarize:
SUMX Percentage =
DISTINCT(
SUMMARIZE(
Input,
INPUT[%],
Input[Name],
Input[Function],
Input[Area],
Input[Tier 1: Process Facet],
Input[Tier 2: Activity Facet],
INPUT[Activity ID],
INPUT[Merged]
))
o59393Post Prodigy
Post ProdigyHi Greg_Deckler
Thanks a lot, the problem is that I still have the Merged column with duplicates.
How could I have instead of 4 rows, just one and a 23%?
I am looking for something like this:
Thanks.