cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aravindh10 Regular Visitor
Regular Visitor

Trying to filter data based on hierarachy

i have manager and delaer data in hierarchy filter.when i select all the dealers or one manager i should get the sum of the goals.when i do not select all the dealers in a particular manager i should not get any value.i have tried this logic

retail_goals =
var counts= CALCULATE(DISTINCTCOUNT(Table1[dealer]),VALUES(Table1[Manager]))
var max_counts=MAXX(SUMMARIZE(Table1,Table1[dealer],Table1[Manager],"max count",COUNT(Table1[dealer])),[max count])
return IF(counts=max_counts,sum(Table1[goals]),0)

i think i am making mistake in finding the max_counts value it should get all the dealers present in the maanger when a manager is selected.you can also suggest different logic.thank you

 

Managerdealergoalscatagory
abcafa1product
abcbaa2service
abccafa3material
abcdafaf4product
defm5service
defn6product
defo2material
defp3product
ghiq4material
ghir6product
ghis2service
ghit5product
ghiu6service
ghiv7material
jklaa8product
jklcc2service
jklbb3product
jkldd4material
jklee1product
jklff2serviceSCRRENSHOT.png

 

2 REPLIES 2
Community Support
Community Support

Re: Trying to filter data based on hierarachy

HI, @aravindh10 

I think you could adjust your formula as below:

retail_goals = 
VAR counts =
    CALCULATE ( DISTINCTCOUNT ( Table1[dealer] ), ALLEXCEPT(Table1,Table1[Manager]))
VAR max_counts =
    MAXX (
        SUMMARIZE (
            ALLSELECTED(Table1),
            --Table1[dealer],  remove this column
            Table1[Manager],
            "max count", COUNT ( Table1[dealer] )
        ),
        [max count]
    )
RETURN
    IF ( counts = max_counts, SUM ( Table1[goals] ), 0 )

and if you should use SUMX instead of MAXX (Blue font) in max_counts value, you could try it by yourself

 

Result:

2.JPGDon't select all dealer3.JPGselect all dealer

 

If not your case, please share your expected output.

 

Best Regards,

Lin

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

Re: Trying to filter data based on hierarachy

i have tried with maxx as well as sumx.when dealers from multiple managers are selected the total goals becomes 0.since the IF ( counts = max_counts) conditions fails it becomes 0.It does not give the goals of the managers whose  all dealers are selected.help me with this screenshot.pnghis

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors