Forum Discussion
Dynamically group
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
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.
4 Replies
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
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.
