Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |