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
ibuhary
Helper I
Helper I

Remove RANKX from Total Row and Hide from Non Related Filters

Hello everyone,

 

I have the few Created Measures in my Database:

 

- StoreRank = RANKX(ALL(Table[Store Code],Table[Store Name]),CALCULATE(SUM(Table[Sales Volume])),,DESC)

 

 - ProductRank = RANKX(ALL(Table[Product Code],Table[Product Name]),CALCULATE(SUM(Table[Sales Volume])),,DESC)

 

I need to know:

 

1. When I Built a Table by Columns Store Code, Store Name, Sales Volume and Store Rank, how not to show any Ranks in the Total at the bottom, at the moment I get a number - 1 rank for the top store and also the total at the bottom also is given a Number - 1 Rank I want to make it blank in there.

 

2. If I populated a Table with Product details and chose to put in one column the Store Rank, it shows Rank - 1 for all the products, I want to make it Blank as well because I didnt chose any columns related to Store in this table.

1 ACCEPTED SOLUTION

@ibuhary

 

For your first question, you can use HASONEFILTER to detect totals/subtotals

 

Something like this

 

- StoreRank =
IF (
    HASONEFILTER ( Table[Store Code] ),
    RANKX (
        ALL ( Table[Store Code], Table[Store Name] ),
        CALCULATE ( SUM ( Table[Sales Volume] ) ),
        ,
        DESC
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
ibuhary
Helper I
Helper I

Did I ask something not logical to do in Power BI ?

@ibuhary

 

For your first question, you can use HASONEFILTER to detect totals/subtotals

 

Something like this

 

- StoreRank =
IF (
    HASONEFILTER ( Table[Store Code] ),
    RANKX (
        ALL ( Table[Store Code], Table[Store Name] ),
        CALCULATE ( SUM ( Table[Sales Volume] ) ),
        ,
        DESC
    )
)

Regards
Zubair

Please try my custom visuals

can you help me on another issue please ?

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.