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

Calculate Hierarchy Above Performance

Hello all, I am trying to set up some measures that work with my hierarchy and report filters to show performance for hierarchy levels above when filtered - eg I select a store and see their performance, district performance, and Market performance as three different lines on a line chart.  I have created measures that work perfectly when using crossfiltering with another visual on the report (a matrix), but they don't seem to work when simply using the filter pane.  When I select the store in the matrix, the line chart filters to show me the three lines - store, district and market all with correct results.  Oddly, when I filter to the store using the Filter Pane (Filter on All Pages), I only see two lines - store and market (the District measure now matches Market).  

 

I'm assuming this has to do with how I have the measures set up between the ALL or ISFILTERED functions, but I'm at a loss.  Any help on how to restructure would be greatly appreciated.

 

Here is the base measure for store performance:

% to Target Filtered = DIVIDE(SUM('Performance'[Actual]),sum('Quota'[Target]))

 

And here are the measures for District and Market level Performance

District % to Target Filtered = calculate(DIVIDE(SUM('Performance'[Actual]),sum('Quota'[Target])),all(Hierarchy[Store])

 

Market % to Target Filtered = calculate(DIVIDE(SUM('Performance'[Actual]),sum('Quota'[Target])),all(Hierarchy[Store],Hierarchy[District]))

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Calculate Hierarchy Above Performance

Hi @sforten,

 

could you share your report? Or create a sample report where you replicate the behaviour you are experiencing?

 

Cheers,
Sturla

Highlighted
Frequent Visitor

Re: Calculate Hierarchy Above Performance

Here is a link to a sample file.  You will see that the graph visual only shows Store and Market when initially using just the filter pane to filter to store 9541, but when you select store 9541 in the matrix, the visual updates to show all three - Store, District, and Market.

 

Desired result is to have all three show up when simply using the Filter Pane to filter to a specific store as the finished report will not have a matrix.

 

https://1drv.ms/u/s!AqL1thqMq4QImxaD0bOLxx_1yFBS?e=MMfU8h 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors