Help with percentage in Matrix table with hierarchy

[yakovlol]

Hello! I have a matrix table with 3 levels of hierarchy (Department-Group - ID). And I need to calculate the percentage for each level of hierarchy but based on Types in a column in that matrix

My matrix 

Result I want to achieve

I tried different measures, but cannot achieve the correct percantage based on my hierarphy raws and columns.

pbix file

Really aprriceted for your help.

Thank you

[Ashish_Mathur]

Hi,

I am not clear about how you have calculated the % in the Pivot table screenshot.  Share the download link of the Excel file with the Pivot Table.

[yakovlol]

Hello @Ashish_Mathur . It's not a calculation in Excel file, in excel I just put the number for the desired picture that I want to achieve in PowerBi. So in Excel not the real calculations

[lucadelicio]

I post a reply, have you seen it?

[lucadelicio]

Try to use the default Show value as - Percent of grand total.

On the field - select the arrow - Show value as - Percent of grand total.

If you resolve your problem mark it as a solution.

Ciao!

[yakovlol]

Hello, @lucadelicio thanks for your reply. But it's not exactly what is needed. If I show the percent of GT I will have values like below 

But I need to have 100% in a row for each type (columns in the header)

I also attached link to pbix file, maybe it could help to understand more the issue.

Thank you

[lucadelicio]

Thanks for the pbix.
Try if this measure is what you are looking for.

Measure =

var _htype =

CALCULATE(

    SUM(Hours[Hours])

    ,ALLEXCEPT(Hours, Hours[Type ])    

)

RETURN

DIVIDE(

    SUM(Hours[Hours])

    ,_htype

)

I hope I have been helpful.
Mark it as a solution if resolve your problem.

Ciao!

[yakovlol]

Hi @lucadelicio thanks for your answer it's almost work as needed. But I need to have 100% in one line as well. I mean all types in columns should give me 100% in a line.

And we have 100%  only for the columns

Maybe do you have anu other sugestions? How can we achieve that?

Thank you)

pbix file

[lucadelicio]

Please share the pbix.
Update the link it doesn't work.

[yakovlol]

Here it is) 
pbix file 

[lucadelicio · Accepted solution]

Try this measure:

Measure 2 =

DIVIDE(

    SUM(Hours[Hours])

    ,CALCULATE(

        SUM(Hours[Hours])

        ,ALL(Hours[Type ])

        ,ALLEXCEPT(Departament, Departament[Departament])

    )

)

[yakovlol]

@lucadelicio thanks for your approach. But with this measure, I indeed have 100% in line, but I missed now hierarchy under Department.

So now for Group 1, Group 2 etc that is under my Departments, we have 100% for each.

But ideally, it should take those 73.77% and divide it between groups and users based on hieraphy 

Maybe there are any other sugestions that could help to resolve it?

Thank you

[lucadelicio]

Is this what you want?

[yakovlol]

yes, indeed. Can you please share a file, or measure fo that?

[lucadelicio]

it is written above....
Is measure 2....
Please mark as a solution.
Thank you

[yakovlol]

Thank you)

[lucadelicio]

Alligator - Peaches 70% = is the result of what calculation?