DAX percentage of hierarchy hitting a target

[Anonymous]

I'm certain this is a common requirement, and I've asked this before, but not quite got the solution I was hoping for and so I'm asking for help in a slightly different way:

I have 2 tables:

1. Employee: Employee Number, Region, District, Name.

2. Sales: Emploee Number, Customer Number, Date, Sales Success. (Sales Success is a Yes or No and 1 line will appear for every sales attempt made on each date).

Relationships are Employee > Sales as a 1-many on Employee Number.

I have these DAX Expressions:

Successful Sales = CALCULATE(Countrows(Sales), Sales[Sales Success] = "Yes")

Attempts = Countrows(Sales)

Sales Rate = DIVIDE([Succesful Sales],[Attempts])

When I drop the Employee Hierarchy onto a Matrix, with the Sales Rate, Drilled to District, this is the resulting matrix:

What I am really really struggling to make work is some DAX that will achieve the following:

Count of how many achieved a target above 20% as a % of the total count of items for the selected level in the hierarchy in the filter context.

So, in the above matrix for North, 2 are achieving Sales Rate > 20% (North 02 and North 04), which I need to return as a % of all Eight Districts = 2 / 8 = 25%

If I were to look at this at the next level down, each District would have a % achieving target based on the Sales Success of the employee count (assuming the employee has data for the date range selected).

I've tried using calculate with counts and using FILTER(ALL( but it never returns the results I'd expect. I've tried creating this table using SummarizeColumns and Summarize but still not achieved a working solution. This seems like it should be really straighforward

HELP, please! (@amitchandak thanks for your previous help, but I've not got the solution working and I am literally tearing my hair out with a deadline approaching. I'm hoping this is a better explanation of the challenge I have).

[v-kelly-msft]

Hi @Anonymous ，

Do you wanna present the result of 25% in the table visual or separately in a card visual?I guess you may need to other measure such as:

measure 2=
var _above=calculate(countrows(table),filter(all(table),[sales rate]>20%))
var _total=calculate(countrows(table),allselected())
Return
divide(_above,_total)

 But I'm not quite sure,if the suggestion above doesnt work,could you pls upload your .pbix file to onedrive business and share the link with us?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@Anonymous , it will level dependent

measure =
var _tab1 =summarize(table,table[region],"_1", DIVIDE([Succesful Sales],[Attempts]))

return
divide(countx(filter(_tab1,[_1]>.2),[region])countx(_tab1,[region]))

//region or level below the region

Different formula a different level refer: https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

@amitchandak You are a lifesaver, thank you so much.

For others finding this example, there's a comma missing it should be:

measure =
var _tab1 =summarize(table,table[region],"_1", DIVIDE([Succesful Sales],[Attempts]))

return
divide(countx(filter(_tab1,[_1]>.2),[region]), countx(_tab1,[region]))

(comma missing before the final counx)

[Anonymous]

It seems like you want to add a measure that shifts contexts appropriately based on drill down.

However, I think you want to calculate it for EACH row in the underlying dataset. Would COUNTX work for you?

I'm not 100% clear on the problem so bear with me here.