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
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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Top Solution Authors