Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I have a table in power BI, something like table below.
i am asked to create graphs(Line and clustered coloumn chart) with top 10/bottom 10 accounts,subserviceline., region and subsector by its variance revenue[(sum(TER_FYTD_USD) minus Sum(TER_PFYTD_USD)] and its variance%
i am trying to find out a way to create a slicer selection as Top 10 and Bottom10
so my client was looking for a slicer where he they can select Top 10 and bottom 10 and the results gets displayed
ho can i achieve this?
Account | DeliveryRegion | ServiceLine | SubServiceLine | TER_FYTD_USD | TER_PFYTD_USD |
Corb | Northeast | Ta | GC | 0 | 0 |
Cost | Southeast | Ta | IT | 0 | 0 |
GPT | Oceania | As | FA | 0 | 0 |
GPT | WEM | As | Au | 0 | -15.098316 |
GPT | FSO EMEIA | Ta | IT | 0 | -1568.27853 |
GPT | Nordics | As | Au | 26937.55 | 13509.15953 |
GPT | WEM | Ta | IT | 0 | 0 |
GPT | FSO APAC | As | FA | 0 | 0 |
GPT | Nordics | TA | CF | 0 | 0 |
Bras | LATAM South | Ta | GC | 0 | 0 |
BBMG | Greater China | As | Au | 0 | 0 |
JP S | India | Ta | GC | 0 | 0 |
JP S | India | Ta | BT | 0 | -4.103596 |
JP S | India | Ta | IT | 1736.059 | 710.157568 |
Hyun | Korea | Ta | IT | 0 | 8508.256101 |
Hyun | Korea | As | Au | 0 | 0 |
Hyun | Korea | Ad | Ri | 0 | 0 |
Hyun | Korea | As | FA | 0 | 0 |
Hyun | Korea | TA | CT | 0 | 0 |
Hyun | MENA | As | Fo | 0 | 0 |
THE | MENA | Ta | BT | 0 | 0 |
THE | MENA | Ta | BT | 0 | 0 |
THE | MENA | As | Au | -2402.8 | 13738.47619 |
THE | MENA | Ta | GC | 12701.28 | 0 |
THE | MENA | Ta | BT | 0 | 0 |
THE | MENA | Ta | In | 0 | 0 |
Solved! Go to Solution.
Please see the attached file it it helps
RANK = IF ( SELECTEDVALUE ( TopBottom[Column1] ) = "Top", RANKX ( ALLSELECTED ( Table1[Account] ), [variance revenue],, ASC, DENSE ), RANKX ( ALLSELECTED ( Table1[Account] ), [variance revenue],, DESC, DENSE ) )
See the attached file now
I added visual filter to hide the Zero values
Hi @vjnvinod
I think the best and possibly only way to achieve this is to filter your visual/page by Top N (top 10) and then set a bookmark. Then set a Top N (bottom 10) and set another bookmark. You can then add a button and bind the bookmark to the button.
I tried it just with a date column
If this helps please mark this as the solution and help others find the answer.
@Anonymous
i have thought about it but not a great solution, especially when it comes to multiple data/queries bookmarking logic will not work, it works only for 1 dataset.
Hi Zubair, any possiblity you can think about it?
i need to give my clients the possiblity of selecting top (5, 10 or 15 or anything) based on his wish and will and also
bottom(5 or 10 or anything)
is there any possiblity this can be achieved in power BI?
please read the thread above
Please see the attached file it it helps
RANK = IF ( SELECTEDVALUE ( TopBottom[Column1] ) = "Top", RANKX ( ALLSELECTED ( Table1[Account] ), [variance revenue],, ASC, DENSE ), RANKX ( ALLSELECTED ( Table1[Account] ), [variance revenue],, DESC, DENSE ) )
thanks so much, very close.
but i noticed its only working for account, not for any other coloumns, see below snapshot.
Also i was wondering, if TER_PFYTD_USD and TER_FYTD_USD=0, is it possible that with some magic, this could be ignored and the next one picks up which is have some realy variance?
anything we can do about it?
See the attached file now
I added visual filter to hide the Zero values
hi Zubair,
there is one more requirement on top of it.
now my client have requested to add a slicer for Variance$ and for Variance%.
how to achieve this?
thanks Zubair it really helped.
i was just testing this
but for subservice line, its throwing up error.(below is the formula i have copied from your file)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |