cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
Highlighted
Community Support Team
Community Support Team

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

Hi @Shruti_Goyal94,

 

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.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

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

Hi @Shruti_Goyal94,

 

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.