cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EmmanuelleC Frequent Visitor
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
erik_tarnvik Established Member
Established Member

Re: Dynamically group

Hi @EmmanuelleC,

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.

 

View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: Dynamically group

@EmmanuelleC 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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
EmmanuelleC Frequent Visitor
Frequent Visitor

Re: Dynamically group

Hi Vanessa,

 

My data look like that

example.png

 

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

 

Highlighted
erik_tarnvik Established Member
Established Member

Re: Dynamically group

Hi @EmmanuelleC,

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.

 

View solution in original post

EmmanuelleC Frequent Visitor
Frequent Visitor

Re: Dynamically group

Perfect, thank you very much

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors