Do the ranking by row grouped by one dimension

[CristinaD]

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:

Area	Market	Product Line	Net Revenues	Rank
Area1	Market1	PL1	50	1
Area1	Market1	PL2	40	2
Area1	Market2	PL1	30	3
Area1	Market2	PL2	20	4
Area2	Market1	PL1	10	4
Area2	Market1	PL2	20	3
Area2	Market2	PL1	30	2
Area2	Market2	PL2	40	1

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

[smpa01]

@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

with Area and product line

Full table

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.

[AlexisOlson]

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.

[smpa01]

Many thanks @AlexisOlson 

[VahidDM]

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:

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

Appreciate your Kudos!!

[smpa01 · Accepted solution]

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

[CristinaD]

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:

Area	Market	Product Line	Net Revenues	Rank
Area1	Market1	PL1	50	1
Area1	Market1	PL2	40	2
Area1	Market2	PL1	30	3
Area1	Market2	PL2	20	4
Area2	Market1	PL1	10	4
Area2	Market1	PL2	20	3
Area2	Market2	PL1	30	2
Area2	Market2	PL2	40	1

But in our data we have this:

Area	Market	Product Line	ColumnX	ColumnY	Net Revenues
Area1	Market1	PL1	abc	abc	50
Area1	Market1	PL2	abc	abc	40
Area1	Market2	PL1	abc	abc	30
Area1	Market2	PL2	abc	abc	20
Area2	Market1	PL1	abc	abc	10
Area2	Market1	PL2	abc	abc	20
Area2	Market2	PL1	abc	abc	30
Area2	Market2	PL2	abc	abc	40

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

Thanks again for the help 🙂

[smpa01]

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

[CristinaD]

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:

Removing Client it works ok!

Removing Market ranking does not work

Same issue if we remove Product Line:

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

Thanks

[smpa01]

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

Now, when I take Market off

and when I take Product Line off

The measure is working as desired.

What would be have been your ideal output?

[CristinaD]

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

Area	Market	_netRevenues	Ranking
Area1	Market1	90	1
Area1	Market2	50	2
Area2	Market1	30	2
Area2	Market2	70	1

Thanks again