Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 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.
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
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!!
@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)
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 🙂
@CristinaD if your data has more column, the same measure provided earlier would still work. You don't need to change anything at all
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
@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
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?
@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
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |