Forum Discussion

o59393's avatar
o59393
Post 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  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.

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

     

7 Replies

  • HotChilli's avatar
    HotChilli
    Community 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
      Post Prodigy
      2 years ago

      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's avatar
      o59393
      Post 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
        Post 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 !!

         

         

  • o59393's avatar
    o59393
    Post Prodigy
    2 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      2 years ago

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

        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.