Unable to display values in Level2 and Level3 Matrix visual in Power Bi

[DevMaster]

I am trying to create Level1, Level2 and Level3 matrix visual and unable to display custom calculations in Level2. below is the sample data to construct the visual.

Data:

Level1	Level2	Level3	Value
A	AA	AAA	5
A	AA	AAAA	10

Matrix Visual: Trying to display as mentioned below in Matrix visual but i am not able to display values in Level2

-A
--AA	50
---AAA	5
---AAAA	10

How can this be handled?

Thanks & Best Regards,

AJ

[Ritaf1983 · Accepted solution]

If I understand you correctly and your desired result is : 

You can modify it from the subtotals format with these steps:

I also updated the linked file

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

[Ritaf1983]

Hi @DevMaster 
Hi the summarized matrix should look like this :

why on your example AA level is 50 if the total sum is 15?

[DevMaster]

thanks for your reply.

I am calculating average (5/10)*100 from backend and the data will come from SQL database. And i just want display the values only on Level2 & Level3

[Ritaf1983]

Hi @DevMaster  again 🙂

I'm still not sure I understand.
But if you just want to show levels 2 and 3 you can achieve it with DAX
According to the hierarchy level, it shows the calculation that you "tell" him

SunValue = if (ISFILTERED('Table'[Level2]),sum('Table'[Value]),if(ISFILTERED('Table'[Level1]),BLANK()))

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

[DevMaster]

i am trying to calculate the percentage and show the values in Level2. Could you please share .pbix file 

[Ritaf1983]

Hi @DevMaster 
If I understand you correctly and your desired result is something like this :

Then you can follow these steps:
1. Add a calculated column for percentage:

Percentage =

 VAR __Current = 'Table'[Value]

  VAR __Previous = MAXX(FILTER('Table','Table'[Level2] = EARLIER('Table'[Level2])),[Level2])

  VAR __PreviousValue = MAXX(FILTER('Table',[Level2]=__Previous),[Value])

RETURN

DIVIDE( __Current,__PreviousValue)

2. Create a measure for the matrix:

MatrixValue = if (ISFILTERED('Table'[Level3]),SUM('Table'[Value]),

if(ISFILTERED('Table'[Level2]),format(min('Table'[Percentage]),"00%"),

blank()))

Link to a sample file 

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

[DevMaster]

Thank you for solution. Here i am facing one issue like i don't want to show Total text in the report. if i set subtotals option to off then values not showing. 

Could you please suggest that how we need to get rid off row/column subtotals.

[Ritaf1983 · Accepted solution]

If I understand you correctly and your desired result is : 

You can modify it from the subtotals format with these steps:

I also updated the linked file

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

[DevMaster · Accepted solution]

Thanks a lot. it resolves all my issues.

[Ritaf1983]

Happy to help, please  consider Accepting  all answers that help with different issues of this post as the solutions to help the other members find it more quickly