Rank measure with hierarchy drill down

[dare]

I applied a Rank measure on an Amount column for Year and Month which gave a pefect result on Pivot table but not on Power view table visual.

what could be the problem please?

[v-shex-msft]

Hi @dare,

Nope, current rank formula not auto summary on different hierarchy levels.

I'd like to suggest you write different rank formulas for different measure formulas form different hierarchy level, then you can if statement to control which formula works on current hierarchy level.

For example: condition measure formula for each hierarchy level.

Measure = 
IF (
    COUNTROWS ( 'Sample' )
        = COUNTROWS (
            FILTER (
                ALLSELECTED ( 'Sample' ),
                FORMAT ( [Date], "mm/dd/yyyy" )
                    = FORMAT ( MAX ( 'Sample'[Date] ), "mm/dd/yyyy" )
            )
        ),
    "Detail level",
    IF (
        COUNTROWS ( 'Sample' )
            = COUNTROWS (
                FILTER (
                    ALLSELECTED ( 'Sample' ),
                    FORMAT ( [Date], "mm/yyyy" ) = FORMAT ( MAX ( 'Sample'[Date] ), "mm/yyyy" )
                )
            ),
        "Month level",
        IF (
            COUNTROWS ( 'Sample' )
                = COUNTROWS (
                    FILTER (
                        ALLSELECTED ( 'Sample' ),
                        YEAR ( [Date] ) = YEAR ( MAX ( 'Sample'[Date] ) )
                    )
                ),
            "Year level"
        )
    )
)

Notice: please replace bold part with your ranking formulas.

BTW, you can also take a look at following link to know how to handing dax calculate on hierarchy total.

Clever Hierarchy Handling in DAX

Regards,

Xiaoxin Sheng

[dare]

Hello Sheng,

I've tried your earlier suggested formular but it worked just like the one I used earlier: Meaning, on Pivot table it works perfect but when using Power View table the ranking doesn't work. 

is it a problem with Power View (including Power BI)? 

[v-shex-msft]

HI @dare,

In fact, it is a common measure totals problem, measure not works properly on total level when you use filter or other specific conditions.

Notice:dax formula calculate drill to detail records and summarize on these result instead direct calculated on summarize records.

In my opinion, I will create a variable table in measure to store summarize records, then do calculation on summarize records.

BTW, You can also take a look at below links which told about this common issue:

Dealing with Measure Totals

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

[dare]

I don't really understand how the measure total issue relates to the rank measure with hierarchy drill down. I'd appreciate if you shed more light on it with an illustrative example.

Many thanks

[v-shex-msft]

HI @dare,

Actually, hierarchy level is generated by multiple fields with parent-child relationship.

You can create a matrix visual with hierarchy column as row field, drag value column to value field(summary mode 'sum') and expand each level on matrix, then you can find values will auto summarize on each hierarchy level.

So when you use hierarchy in visual, each level field are as same as sub total level.(power bi will try to summarize detail records based on your hierarchy level field)

Regards,

Xiaoxin Sheng

[dare]

Thanks for your Repy Sheng.

[dare]

Many Thanks Sheng. I'd try your suggestion when I resume work on Monday and feed you back.

I appreciate!