How to return unique column values with summarize with distinct

[o59393]

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  seen below:

The percentage of that table created should be 100%, however I am getting 97%:

The difference is comming from the combination digital transformation + dashboard hubs, where it should be 23% and not 20%

How could I get the table below where the column A has no duplicates and the column B has 100%?

 See attached pbix and xls with desired solution

SUMMARIZE.xlsx

summarize.pbix

THanks.

[HotChilli · Accepted solution]

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] ))

[o59393]

Hi @HotChilli 

I tried 

SUMX Percentage = 

var _tab = SUMMARIZECOLUMNS(
    Template[Tier 1: Process Facet],
    Template[Tier 2: Activity Facet],
    Template[Activity ID],
    Template[Name],
    Template[Function],
    Template[Area],
    Template[Activity ID],
    "perC", MIN(Template[% of your annual time in 2023 dedicated to the activity])//,
 )

RETURN
GROUPBY(_tab, 
Template[Tier 1: Process Facet],
Template[Tier 2: Activity Facet],
Template[Name],
Template[Function],
Template[Area],
    "total", SUMX(CURRENTGROUP(), [perC] ))

But gave me this error:

The column 'Activity ID' was specified more than once in the 'SUMMARIZECOLUMNS' function.

To your solution I just need to add to the table the columns name, function and area and it will be perfect.

Thanks for your help.

[o59393]

I made it @HotChilli !

Here is the final solution on my model

SUMX Percentage = 

var _tab = SUMMARIZECOLUMNS(
    Template[Tier 1: Process Facet],
    Template[Tier 2: Activity Facet],
    Template[Activity ID],
    Template[Name],
    Template[Function],
    Template[Area],
    "perC", MIN(Template[% of your annual time in 2023 dedicated to the activity])//,
 )

RETURN
GROUPBY(_tab, 
Template[Tier 1: Process Facet],
Template[Tier 2: Activity Facet],
Template[Name],
Template[Function],
Template[Area],
    "total", SUMX(CURRENTGROUP(), [perC] ))

Thanks for your guide !!

[o59393]

Hi @HotChilli 

how can i include these 3 columns?

    Input[Name],
    Input[Function],
    Input[Area],

I tried but will give me an error.

Thanks a lot!

[o59393]

If I use the code:

SUMX Percentage = 


DISTINCT(
SUMMARIZE(
    Input,
    Input[Name],
    Input[Function],
    Input[Area],
    Input[Tier 1: Process Facet],
    Input[Tier 2: Activity Facet],
    INPUT[Merged]
))

The column Merge will have unique values, therefore the duplicate values occure when the column 'INPUT[%]'  is added to the code above.

[Greg_Deckler]

@o59393 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]
))

[o59393]

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