cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dare Frequent Visitor
Frequent Visitor

Rank measure with hierarchy drill down

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?

7 REPLIES 7
Community Support Team
Community Support Team

Re: Rank measure with hierarchy drill down

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
dare Frequent Visitor
Frequent Visitor

Re: Rank measure with hierarchy drill down

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

I appreciate!
dare Frequent Visitor
Frequent Visitor

Re: Rank measure with hierarchy drill down

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

Community Support Team
Community Support Team

Re: Rank measure with hierarchy drill down

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
dare Frequent Visitor
Frequent Visitor

Re: Rank measure with hierarchy drill down

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
Community Support Team
Community Support Team

Re: Rank measure with hierarchy drill down

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
dare Frequent Visitor
Frequent Visitor

Re: Rank measure with hierarchy drill down

Thanks for your Repy Sheng.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 41 members 957 guests
Please welcome our newest community members: