Forum Discussion

Rice's avatar
Rice
Icon for Helper I rankHelper I
6 years ago
Solved

Rankx in Matrix Hierarchy

Hello All!


New Power BI user here, I'm trying to work out a ranking system that will work in a matrix as per below and struggling by quite a bit. My current dax produces the current result below, but I'd prefer the desired result as the below visual. In words, I just want the rank of the total count of causes across the selected company. Any help would be much appreciated.

RAW Data: Columns: Company, Causes, Locations, all in a single table.

Simplified example: https://mega.nz/#!SNpzQQqJ!GteVdLHPUBnNi0MwCf_U_Z0yzfrb3VIQXcHFPDdMDzM

 

TopN: MS - Alerts By Cause =
\\ Rank Variable performs the rank action across the count of all causes ignoring blanks.
VAR Ranks =
IF(
NOT(ISBLANK([CountCause])),
RANKX(
CALCULATETABLE(
VALUES('MS - Incident Report'[Cause]),
ALLSELECTED('MS - Incident Report'[Cause])
),
[CountCause],
,
DESC,
Dense
))

\\ Returns based on a variable selection of top ranks
RETURN
IF (
ISBLANK ( 'TopN'[TopN Value] ),
Ranks,
IF ( Ranks <= 'TopN'[TopN Value], Ranks, BLANK () )
)


Current Result


DesiredResult

COUNT
DAX
Dax Help
DAX RANKX
Need Help
  • Rice's avatar
    Rice
    6 years ago

    The following code worked for my purposes in case anyone comes across this issue again. (Appreciate all the help!, PowerBi discord was great help as well.)

     

    TopN: MS - Alerts By Cause = 
Var Ranks =
CALCULATE(
    RANKX(
    ALL('MS - Incident Report'),
    CALCULATE(
        COUNTROWS('MS - Incident Report'),
        ALLEXCEPT('MS - Incident Report', 'MS - Incident Report'[Cause],'MS - Incident Report'[Location])),,
    DESC,
    Dense
        )
    )
RETURN
    IF (
        ISBLANK ( 'TopN'[TopN Value] ),
        ranks,
        IF ( Ranks <= 'TopN'[TopN Value], [CountCause], BLANK () )
    )

     

8 Replies

  • lc_finance's avatar
    lc_finance
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi Rice ,

     

     

    could you share a sample Power BI file?

    That will make it easier to help you. 

     

    You can share it using One Drive, Google Drive or other similar tool.

     

    Regards,

     

    LC

      • lc_finance's avatar
        lc_finance
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Hi Rice ,

         

         

        If I understand correctly, you want the ranking of the causes to not be dependent on the location?

        For example, the cause 'OMG' should have a global ranking that does not depend on different locations.

         

        If that is what you want, you can find an updated DAX formula here. I add a CALCULATE and an ALL for [CountClause] so that the count is done independently of the location.

         

        Here is it:

        TopN: MS - Alerts By Cause = 
// Rank Variable performs the rank action across the count of all causes ignoring blanks.
VAR Ranks =
IF(
NOT(ISBLANK([CountCause])),
RANKX(
CALCULATETABLE(
VALUES('MS - Incident Report'[Cause]),
ALLSELECTED('MS - Incident Report'[Cause])
),
CALCULATE([CountCause],ALL('MS - Incident Report'[Location])),
// [CountCause],
,
DESC,
Dense
))

// Returns based on a variable selection of top ranks.
RETURN
IF (
ISBLANK ( 'TopN'[TopN Value] ),
Ranks,
IF ( Ranks <= 'TopN'[TopN Value], Ranks, BLANK () )
)

         

        Hope this helps you!

         

        LC

        Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com