topic Re: Do the ranking by row grouped by one dimension in DAX Commands and Tips

Do the ranking by row grouped by one dimension

CristinaD — Tue, 09 Nov 2021 16:04:06 GMT

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

Re: Do the ranking by row grouped by one dimension

smpa01 — Tue, 09 Nov 2021 16:23:05 GMT

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)

Re: Do the ranking by row grouped by one dimension

VahidDM — Tue, 09 Nov 2021 23:06:54 GMT

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

Re: Do the ranking by row grouped by one dimension

CristinaD — Wed, 10 Nov 2021 09:52:58 GMT

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 🙂

Re: Do the ranking by row grouped by one dimension

smpa01 — Wed, 10 Nov 2021 12:28:03 GMT

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

Re: Do the ranking by row grouped by one dimension

CristinaD — Fri, 12 Nov 2021 10:09:33 GMT

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

Re: Do the ranking by row grouped by one dimension

smpa01 — Fri, 12 Nov 2021 14:18:46 GMT

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?

Re: Do the ranking by row grouped by one dimension

CristinaD — Fri, 12 Nov 2021 15:15:28 GMT

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

Re: Do the ranking by row grouped by one dimension

smpa01 — Fri, 12 Nov 2021 18:35:15 GMT

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.

Re: Do the ranking by row grouped by one dimension

AlexisOlson — Fri, 12 Nov 2021 21:10:51 GMT

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.

Re: Do the ranking by row grouped by one dimension

smpa01 — Fri, 12 Nov 2021 23:07:04 GMT

Many thanks AlexisOlson