cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Top and others with use of filter and represent it in graph

Hi Guys,

 

This is a little complicated problem, I have a table which has data conatisn few columns named URL Domain, Region and Impact. 

URL DomainRegionImpact
udf.dkDNK0.0345
vpo.dkDNK0.1234
123in.dkDNK0.0658
bee.dkDNK0.05478
efsh.esESP0.234
ahle.esESP0.00654
1n1.esESP0.0876
bonus.esESP0.0238
acd.frFRA0.1834
abnb.frFRA0.0564
bonj.frFRA0.8723
abc.comGBR0.004522
def.comGBR0.1235
123.ukGBR0.0398
360.ukGBR0.04587

 

 

No I want to represent Top 2 URL domain based on Impact and region will be a filter. 

So when I click GBR then Top 2 for GBR shoudl show up and rest of the GBR domain should add upto Others

def.comGBR0.1235
360.ukGBR0.04587
OthersGBR0.044322

 

And I want this to repsent the above table in a graph. 

Help is very much appreciated. This is really important problem.

 

Cheers

Shruti

 

Thank you so much in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Top and others with use of filter and represent it in graph

Hi @Anonymous,

 

Please follow the steps below.

 

1. Create an index column in Query Editor.

 

2. Create the calculated column below.

Category =
VAR r =
    RANKX (
        FILTER ( 'Table1', 'Table1'[Region] = EARLIER ( Table1[Region] ) ),
        'Table1'[Impact],
        ,
        DESC
    )
RETURN
    IF ( r <= 2, 'Table1'[URL Domain], "others" )

3. Create the measure with the formula below.

Measure =
IF (
    MAX ( 'Table1'[Category] ) = "others",
    CALCULATE (
        SUM ( 'Table1'[Impact] ),
        FILTER ( 'Table1', 'Table1'[Category] = "others" )
    ),
    MAX ( 'Table1'[Impact] )
)

4. Then you will get your desired output.

 

output.PNG

 

More details, you could refer to the attachment. You also could refer to the blog.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Top and others with use of filter and represent it in graph

Hi @Anonymous,

 

Please follow the steps below.

 

1. Create an index column in Query Editor.

 

2. Create the calculated column below.

Category =
VAR r =
    RANKX (
        FILTER ( 'Table1', 'Table1'[Region] = EARLIER ( Table1[Region] ) ),
        'Table1'[Impact],
        ,
        DESC
    )
RETURN
    IF ( r <= 2, 'Table1'[URL Domain], "others" )

3. Create the measure with the formula below.

Measure =
IF (
    MAX ( 'Table1'[Category] ) = "others",
    CALCULATE (
        SUM ( 'Table1'[Impact] ),
        FILTER ( 'Table1', 'Table1'[Category] = "others" )
    ),
    MAX ( 'Table1'[Impact] )
)

4. Then you will get your desired output.

 

output.PNG

 

More details, you could refer to the attachment. You also could refer to the blog.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
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