Dynamically group

[Anonymous]

Hello,

I have a table with name of report, date of creation and creator.

I would like to group creator. I need them to keep their name if they create 5%  or more of the total pool of report. If they creadted less than 5% of report, they have to by group in "Other"

I can group them manualy but I will have to check evrey month if they are ine the right category

Do you know a way to do so dynamically?

Thank in advance,

Emmanuelle

[vanessafvg]

@Anonymous you can use a switch statement probably to derive it, however can you show me what your data looks like?  otherwise it will be hard to show you how to write it

[Anonymous]

Hi Vanessa,

My data look like that

I have the creator name, the date of creation and the raport title

I would like to group C and D because they produce less then 5% of the report total

[erik_tarnvik · Accepted solution]

Hi @Anonymous,

I would suggest that you create a table with only the creator names. This can be done by clicking Create Table and enter the following formula:

Creators = DISTINCT(Reports[Creator])

(assuming your table is called Reports.) Now establish a relationship between Creators and Reports on the Creator field.

Next step is to define a calculated new column called Group in the Creators table:

Group = 
VAR C = Creators[Creator] 
RETURN 
IF(CALCULATE(COUNTROWS(Reports),Reports[Creator] = C)/COUNTROWS(Reports) > 0.05, 
   C,
   "Other") 

In your table visual you can now use Creators[Group] instead of Reports[Creator] and you should get the desired result.

[Anonymous]

Perfect, thank you very much