Show total with Hierarchy RLS

[Domenico96]

Hello,

I have a problem. I have the table dm_territory that filter the ft_visit. I set the hierarchy RLS from the territory to the visit. While the child are correctly showed, I would like to obtain that when a child is filtered, he can also see the total of the parent but without details. 
For example : 

Level1	Level2	leve3	Visit
A			100
	B		60
		b.1	20
		b.2	20
		b.3	20
	C		40
		c.1	40

when I enter the report with b1, In my matrix I would like to see the details of 20, but in the total B I would like to see 60 for B and 100 for A, not 20. 

I'm using a simple dax for the visit : SUM (Table[Number of visit])

Thank you 

[v-hjannapu]

Hi @Domenico96,

I would also take a moment to thank @johnt75  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @Domenico96,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[v-hjannapu]

Hi @Domenico96,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Community Support Team.

[johnt75 · Accepted solution]

You would need to build an aggregate table to hold the totals for Level2. Have a look at https://www.sqlbi.com/articles/computing-accurate-percentages-with-row-level-security-in-power-bi/ and https://www.sqlbi.com/articles/implement-non-visual-totals-with-power-bi-security-roles/ . They should give you a good idea of how to set up the model and what measures you will need.