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

[Anonymous]

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 Domain	Region	Impact
udf.dk	DNK	0.0345
vpo.dk	DNK	0.1234
123in.dk	DNK	0.0658
bee.dk	DNK	0.05478
efsh.es	ESP	0.234
ahle.es	ESP	0.00654
1n1.es	ESP	0.0876
bonus.es	ESP	0.0238
acd.fr	FRA	0.1834
abnb.fr	FRA	0.0564
bonj.fr	FRA	0.8723
abc.com	GBR	0.004522
def.com	GBR	0.1235
123.uk	GBR	0.0398
360.uk	GBR	0.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.com	GBR	0.1235
360.uk	GBR	0.04587
Others	GBR	0.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

[v-piga-msft · Accepted solution]

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.

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

Best  Regards,

Cherry