Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vjnvinod
Impactful Individual
Impactful Individual

Top10 and bottom 10 by revenue delta

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%

[SUM([TER_PFYTD_USD])/SUM([TER_FYTD_USD])]
 

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?

 

AccountDeliveryRegionServiceLineSubServiceLineTER_FYTD_USDTER_PFYTD_USD
CorbNortheastTaGC00
CostSoutheastTaIT00
GPTOceaniaAsFA00
GPTWEMAsAu0-15.098316
GPTFSO EMEIATaIT0-1568.27853
GPTNordicsAsAu26937.5513509.15953
GPTWEMTaIT00
GPTFSO APACAsFA00
GPTNordicsTACF00
BrasLATAM SouthTaGC00
BBMGGreater ChinaAsAu00
JP SIndiaTaGC00
JP SIndiaTaBT0-4.103596
JP SIndiaTaIT1736.059710.157568
HyunKoreaTaIT08508.256101
HyunKoreaAsAu00
HyunKoreaAdRi00
HyunKoreaAsFA00
HyunKoreaTACT00
HyunMENAAsFo00
THEMENATaBT00
THEMENATaBT00
THEMENAAsAu-2402.813738.47619
THEMENATaGC12701.280
THEMENATaBT00
THEMENATaIn00
2 ACCEPTED SOLUTIONS

@vjnvinod 

 

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 )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

@vjnvinod 

 

See the attached file now

 

I added visual filter to hide the Zero values

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

 

Screenshot_4.png

Screenshot_5.png

Screenshot_6.png

 

If this helps please mark this as the solution and help others find the answer.

vjnvinod
Impactful Individual
Impactful Individual

@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.

 

@Zubair_Muhammad 

 

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

@vjnvinod 

 

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 )
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad 

 

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?

 

topbottom.PNG

@vjnvinod 

 

See the attached file now

 

I added visual filter to hide the Zero values

 

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad 

 

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?

@Zubair_Muhammad 

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)

RANK_Subserviceline =
IF (
    SELECTEDVALUE ( TopBottom[Column1] ) = "Top",
    RANKX ( FILTER( ALLSELECTED('Account Listing'[SubServiceLine] ),CALCULATE(sum('Account Listing'[TER_FYTD_USD])<>0||CALCULATE(sum('Account Listing'[SubServiceLine])<>0))), [Variance$],, ASC, DENSE ),
    RANKX ( FILTER( ALLSELECTED('Account Listing'[SubServiceLine] ),CALCULATE(sum('Account Listing'[TER_FYTD_USD])<>0||CALCULATE(sum('Account Listing'[TER_PFYTD_USD])<>0))), [Variance$],, DESC, DENSE )
)
 
VF_subserviceline = if([RANK_Subserviceline]<=[Parameter Value],1,0)

 

errortop10.PNG

vjnvinod
Impactful Individual
Impactful Individual

@Zubair_Muhammad 

 

i just removed the sum and it worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.