Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

How 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...
Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    2 years ago

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

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago

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's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    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 !!