Rankx in Matrix Hierarchy

[Rice]

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

[lc_finance]

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

[Rice]

Sorry for not including a file initially @lc_finance . Took me a little bit to draft this simplified example up.

Here you are:

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

Appreciate the help!

~Rice

[lc_finance]

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

[Rice]

Not quite what I'm looking for as of yet @lc_finance . My apologies for being unable to explain it more clearly.

I just want the highest 10 results of the total count of causes for the respective company selected then to display correctly in a matrix as below.

[lc_finance]

Hi @Rice ,

to understand better: the overall cause ranked 3 might also exist in locations A and B and not only. How to decide to only show it under location 3?

Could you share a table of the desired result based on your sample data?

That would likely make it easier to understand.

Regards,

LC

[Rice]

@lc_finance, I have used the sample data set I provided but buffed the numbers a wee bit to get the point across. The causes can be associated to multiple different locations. Think "Network Disconnected" or "CPU Usage too high". The count should be an overall count of causes for the selected company then rank based on that. 

Appreciate the help once again!

~Rice

[Rice · Accepted solution]

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

[lc_finance]

Hi @Rice ,

sorry for the delay in my reply!

I am glad you were able to find a solution.

LC