Calculate percentage at hierarchy level

[Mike22]

Hello Friends, 

I am trying to do a very simple measure but I am really struggling, I searched everywhere but cannot find a solution. What I am trying to achieve is a measure to use as a column on a hierarchy to calculate the percentage of each value in the hierarchy. 

I tried the following but it does not seem to work. 

Percentage =

var totalgender = CALCULATE(DISTINCTCOUNT(Stafflist[personnelnumb.]),ALLSELECTED(Stafflist[Gender]))

var totalstaff= CALCULATE(DISTINCTCOUNT(Stafflist[personnelnumb]),ALLEXCEPT(Stafflist,Stafflist[personnelnumb]))

return

totalgender/totalstaff

what I am struggling with is the totalstaff counting the staff only in the hierarchy 

Percentage has to show 48% for Female and 52% for male in level 4 and 38% for male and 62%for Female in level 3. The measure has to be dynamic in order to update as slicers are filtering and reducing the number of staff on the page. Below is the results I am getting instead. 

Level 4


Would appreaciate if anyone can point me in the right direction. 

Thanks,

Mike

[Anonymous]

Hi @Mike22 ,

Kindly use the below measue to achive your request.

Measure:
Percent =
VAR Total = CALCULATE(SUM('Sample'[Staff]),ALL('Sample'),VALUES('Sample'[Level]))
RETURN
CALCULATE(DIVIDE(SUM('Sample'[Staff]),Total,0))

Output:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

we call your table staff, with Gender, Level and Persons columns.

You could create these measures for your first target:

# Persons = SUM(Staff[Persons])

# Level Persons = CALCULATE([# Persons],ALLEXCEPT(Staff,Staff[Level]))

# Total Persons = CALCULATE([# Persons],ALL(Staff))

% of Level = IF( ISINSCOPE(Staff[Gender]), DIVIDE([# Persons], [# Level Persons]))

% of Total = DIVIDE([# Persons], [# Total Persons])

This could be a starting point because if you want manage other filters and slicers you have to work on the ALLEXCEPT.

Regards

Lorenzo

[Mike22]

Hi Lorenzo @Anonymous , 

Thank you for the suggestion but it does not work. Just sharing below the dataset to facilitate.

Table = Stafflist

Person Number	Gender	Level
1	M	3
2	F	3
3	F	3
4	F	4
5	M	4
6	M	3
7	M	4
8	F	3
9	F	4
10	M	3

The matrix rows are level and Gender. The columns are the count of person number and the measure I am trying to create to give me the percentage. 

Hope this can clarify. 

@parry2k  Thanks for replying, above is the dataset. 

Thanks. 

Mike

[Anonymous]

Hi @Mike22 ,

Person Number is the number of the persons or a PersonID.

This doesn't make great difference for my solution but I would know to better fit my sample.

I verify my solution and it's correct.

Using your data assuming Person Number as number of persons:

But this one works also assuming Person Number as PersonID.

Look at my sample: Percentage of Parent 

Regards

Lorenzo

[dax]

Hi Mike22,

You could refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax ,

our solutions are the same!

👍

Lorenzo

[Mike22]

@dax @Anonymous 

Thank you both, especially for the very quick answer.  I think my issue could be because of the realtionship between various tables and how I am using them. I have attached how the actual dataset is with the various related tables. You will see that now the percentage does not work, especially if you filter with the slicer. 

Thanks,

Mike

https://file.io/qtomU8  (not sure how to attach a file)

[Anonymous]

Hi @Mike22 ,

the link doesn't work.

[Mike22]

Try copy pasting in the browser. 

https://file.io/qtomU8

Thanks!

[Mike22]

@Anonymous  it stopped working for me as well. 

Try below

https://file.io/L5835e

[Mike22]

@Anonymous 

this should work

https://wsi.li/l58QzwNBxpI9m8

[dax]

Hi Mike22,

You could try below measure to see whether it work ot not.

Measure 3 = COUNT(T2[Person Number])/CALCULATE(COUNT(T2[Person Number]),filter( ALL(T2), T2[Level]=MIN(T2[Level])))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mike22]

Thanks @dax 

Unfortunately it does not work. Results below. I am really confused. 

[Anonymous]

Hi,

Try with this one:

Measure 2 = COUNT(T2[Person Number])/CALCULATE(COUNT(T2[Person Number]), ALLEXCEPT(T2,'Level Ordering'[Level]))

Lorenzo

[Mike22]

Thank you @Anonymous 

I am getting the same results as the previous screen shot. Anyway you can include it in the pbix file I had previosuly shared? As soon as I try to filter the results become incorrect. 

Thanks!

[dax · Accepted solution]

Hi Mike22,

I am sorry, I didn't take your filter into consideration previously. Now you could try below to see whether it work or not.

Measure 3 = if(ISFILTERED(Entity[Entity]),COUNT(T2[Person Number])/CALCULATE(COUNT(T2[Person Number]),filter( ALLSELECTED(T2), T2[Level]=MIN(T2[Level]) && T2[Entity]=MIN(Entity[Entity]))),COUNT(T2[Person Number])/CALCULATE(COUNT(T2[Person Number]),filter( ALLSELECTED(T2), T2[Level]=MIN(T2[Level]) )))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mike22]

Thank you very much! This worked and also thought me a few more things about dax!

[parry2k]

@Mike22 how you sample data looks like can you share?