cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CristinaD
Frequent Visitor

Do the ranking by row grouped by one dimension

Hi,

I am trying to rank a table with more than one dimension displayed. Also this ranking should be grouped at the level of one dimension. Let me put an example table:

AreaMarketProduct LineNet RevenuesRank
Area1Market1PL1501
Area1Market1PL2402
Area1Market2PL1303
Area1Market2PL2204
Area2Market1PL1104
Area2Market1PL2203
Area2Market2PL1302
Area2Market2PL2401


As you can see in the table before we have a rank by Net Revenues where each Area has its own ranking. Also this ranking has to respond to other filters therefore it would have to be a measure, not a calculated column.

Could anyone help me to build the dax formula to find this ranking?

Thank you,

Cristina

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@CristinaD  you can get ranking by sub cat like following

_netRev = SUM('Table'[Net Revenues])
_ranking = 
VAR _maxArea = MAX('Table'[Area])
RETURN RANKX(FILTER(ALLSELECTED('Table'),'Table'[Area]=_maxArea),[_netRev],,DESC)

 

smpa01_0-1636474957261.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

10 REPLIES 10
smpa01
Super User
Super User

@CristinaD  I looked intot it and came to a solution, please apply this at your end and see if it resolves the issue.

You need following measures

 

_netRev =
SUM ( 'Table'[Net Revenues] )


_rank1_1 =
RANKX (
    ALL ( 'Table'[Product Line] ),
    CALCULATE ( SUM ( 'Table'[Net Revenues] ) ),
    ,
    DESC
)


_rank1_2 =
RANKX (
    ALL ( 'Table'[Market] ),
    CALCULATE ( SUM ( 'Table'[Net Revenues] ) ),
    ,
    DESC
)

_rank2 =
VAR _maxArea =
    MAX ( 'Table'[Area] )
RETURN
    RANKX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Area] = _maxArea ),
        [_netRev],
        ,
        DESC
    )


DesiredRanking =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Area] ) = TRUE ()
        && ISINSCOPE ( 'Table'[Market] ) = TRUE ()
        && ISINSCOPE ( 'Table'[Product Line] ) = TRUE (), [_rank2],
    ISINSCOPE ( 'Table'[Area] ) = TRUE ()
        && ISINSCOPE ( 'Table'[Market] ) = TRUE ()
        && ISINSCOPE ( 'Table'[Product Line] ) = FALSE (), [_rank1_2],
    ISINSCOPE ( 'Table'[Area] ) = TRUE ()
        && ISINSCOPE ( 'Table'[Market] ) = FALSE ()
        && ISINSCOPE ( 'Table'[Product Line] ) = TRUE (), [_rank1_1]
)

 

with Area and Market

smpa01_2-1636741542406.png

with Area and product line

smpa01_3-1636741573847.png

Full table

smpa01_4-1636741611373.png

 

pbix is attached.

 

I don't know if there is a better way rather than using a switch to gives us what we need @Greg_Deckler @AlexisOlson @OwenAuger  - in Summary OP wants the dynamic ranking of Market by Area or dynamic ranking of Product by Area or dynamic ranking of Product-Market combination by Area to take place based on what he/she makes available in the filter context. I have solved it in a way, seeking your opinion to see if there is a better way to achieve this.

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


I couldn't come up with something much better but here's something different along the lines of this post I wrote a while back.

 

Define a calculated summary table that has all the subtotals you need:

Summary = 
SUMMARIZECOLUMNS (
    'Table'[Area],
    ROLLUPADDISSUBTOTAL ( 'Table'[Market], "IsMarketRollup" ),
    ROLLUPADDISSUBTOTAL ( 'Table'[Product Line], "IsProductRollup" ),
    "NetRev", CALCULATE ( SUM ('Table'[Net Revenues] ) )
)

 

Then define a measure that ranks the [_netRev] in the current context against the appropriate subtotal.

RankNetRev = 
VAR MarketScope  = ISINSCOPE ( 'Table'[Market] )
VAR ProductScope = ISINSCOPE ( 'Table'[Product Line] )
VAR CurrNetRev = [_netRev]
RETURN
IF (
    MarketScope || ProductScope,
    RANKX (
        FILTER (
            Summary,
            [Area] IN VALUES ('Table'[Area] )
                 && [IsMarketRollup]  = NOT ( MarketScope )
                 && [IsProductRollup] = NOT ( ProductScope )
        ),
        [NetRev],
        CurrNetRev,
        DESC
    )
)

 

This falls apart if need the rank to be dynamically responsive to filters though.

Many thanks @AlexisOlson 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


VahidDM
Super User
Super User

Hi @CristinaD 

 

Try this measure:

Rank Column DAX = 
VAR _A =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Area], 'Table'[Net Revenues] ),
        "RankX",
            RANKX (
                FILTER ( ALL ( 'Table' ), 'Table'[Area] = EARLIER ( 'Table'[Area] ) ),
                'Table'[Net Revenues],
                ,
                DESC,
                DENSE
            )
    )
RETURN
    MAXX ( _A, [RankX] )

 

Output:

VahidDM_0-1636499204273.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

 

smpa01
Super User
Super User

@CristinaD  you can get ranking by sub cat like following

_netRev = SUM('Table'[Net Revenues])
_ranking = 
VAR _maxArea = MAX('Table'[Area])
RETURN RANKX(FILTER(ALLSELECTED('Table'),'Table'[Area]=_maxArea),[_netRev],,DESC)

 

smpa01_0-1636474957261.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


CristinaD
Frequent Visitor

Hi, 

Thanks for the solution, it works if the table only contains these columns, But what if our table has more fields? Example:

We want to display this on the object:

AreaMarketProduct LineNet RevenuesRank
Area1Market1PL1501
Area1Market1PL2402
Area1Market2PL1303
Area1Market2PL2204
Area2Market1PL1104
Area2Market1PL2203
Area2Market2PL1302
Area2Market2PL2401


But in our data we have this:

AreaMarketProduct LineColumnXColumnYNet Revenues
Area1Market1PL1abcabc50
Area1Market1PL2abcabc40
Area1Market2PL1abcabc30
Area1Market2PL2abcabc20
Area2Market1PL1abcabc10
Area2Market1PL2abcabc20
Area2Market2PL1abcabc30
Area2Market2PL2abcabc40

I have tried with the all except but we cannot use it because there are too many columns.

Thanks again for the help 🙂

@CristinaD  if your data has more column, the same measure provided earlier would still work. You don't need to change anything at all

smpa01_0-1636547274941.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


CristinaD
Frequent Visitor

Thanks again for the quick answer @smpa01 ,

The problem comes when we are not using all the columns of the data, but the error does not happen on all columns. Example:

Here we are using all the dataset and works ok:

CristinaD_0-1636709890150.png

 

Removing Client it works ok!

CristinaD_1-1636709890157.png

 

Removing Market ranking does not work

CristinaD_2-1636709890166.png

 

Same issue if we remove Product Line:

CristinaD_3-1636709890170.png

 

If you can help me on that I will greatly appreciate it

Thanks

@CristinaD  your end goal is to create a ranking measure of each area based on the reevenue

 

The following ranking measure

_ranking = 
VAR _maxArea = MAX('Table'[Area])
RETURN RANKX(FILTER(ALLSELECTED('Table'),'Table'[Area]=_maxArea),[_netRev],,DESC)

 

is taking the value of _netRev and then does the ranking of _netRev value against what the internal of DAX generates based on what axis is you bring over.

 

For example, when all the columns are available, it is self explanatory 

why {Area1,PL1,Market2,30} is ranked as 3 cause 30 is in the 3rd position in 50,40,30,20. If you take a look at each _netRev and compare against

Ranking _netRev Against what? you will understand clearly.

 

smpa01_0-1636726449795.png

 

Now, when I take Market off

smpa01_1-1636726632262.png

 

and when I take Product Line off

smpa01_2-1636726670422.png

The measure is working as desired.

 

What would be have been your ideal output?

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


CristinaD
Frequent Visitor

@smpa01 thanks now I understand the logic better! I did not understand the ranking such that. The problem is that the ranking would have to adapt to current values. Example of result taking product Line off.

AreaMarket_netRevenuesRanking
Area1Market1901
Area1Market2502
Area2Market1302
Area2Market2701


Thanks again

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors