Matrix Visual Total Row

[harmishPatel]

I want to create something like below mockup in Power BI

Related Cases Report in Power BI with potential mock-up edits:
Lead Case	Related Cases	Project or Initiative Case Count	Case Status	Peer Hours Worked	Hours Worked on Case (Owner)	Total Hours Worked (Peer + Owner)
2022-1487			Comp.	1	2	3
	2022 - 1393		Active	2	5	7
	2022 - 1543		Pend..	3	4	7
	2022 - 1544			1	1	2
	2022 - 1545			2	2	4
	2022 - 1546			1	5	6
	2022 - 1547			2	6	8
Totals		7		12	25	37
2022-1488			Active	1	2	3
	2022 – 1394		Com..	2	5	7
	2022 - 1546		Active	3	4	7
Totals		3		6	11	17

Right know I can only able to achieve like this

I am getting this result through matrix visual.

I have three tables:

1. Lead Case Detail (which contains all the information about the lead case ID)
2. Related Case Detail (which contains all the information about the related case ID)
3. Transaction Table (which shows the lead case ID and the associated related case IDs)

This are the measures I am using

1. Total Hours Worked

Total Hours Worked =

//Gets the value of the Hours Worked for the Lead Case

VAR CurrentCase = LOOKUPVALUE(

    'Lead Case'[Total Hours Worked],

    'Lead Case'[Lead Case],

    SELECTEDVALUE('Transcation'[Lead Case])

)

//Gets the value of the Hours Worked for the Related Case

VAR RelatedCase = LOOKUPVALUE(

    'Link Case'[Total Hours Worked],

    'Link Case'[Case Id],

    SELECTEDVALUE('Transcation'[relatedcaseid])

)

//Gets all the values of the Case Id for the Lead Case

//We use this to check if the hierarchy is in Total or not

VAR CurrentCaseValues = VALUES('Lead Case'[Case Id])

RETURN

SWITCH(

    TRUE(),

    NOT ISINSCOPE('Lead Case'[Case Id]),

    'Key Measures'[Sum of Total Hours Worked on Case],

    SELECTEDVALUE('Lead Case'[Case Id]) IN CurrentCaseValues

    &&

    NOT ISINSCOPE('Link Case'[Case Id]),

    CurrentCase,

    RelatedCase

)

2. Case Count

Case Count =

IF (

    NOT ISINSCOPE ( 'Link Case'[Case Id] ),

    DISTINCTCOUNT ( 'Transcation'[Lead Case] ) + COUNT ( 'Transcation'[relatedcaseid] )

)

and so on...

The main thing is that I want a total row after each main case ID, which shows the total hours of the main and related cases.

[Anonymous · Accepted solution]

Hi @harmishPatel ,

Thanks for Greg_Deckler 's reply!

And @harmishPatel , The format of having multiple rows of Total in the middle of the matrix like this is not supported in the Matrix visual object in Power BI Desktop,

the most you can achieve is to display the value of Total on the parent node like you have already implemented.

Also in the screenshot you provided on your side it seems that the total for Case Count is correct and the totals for the other two columns are wrong, you can try to create these two additional measures:

Total Hours Worked = SUMX(VALUES('Table'[Case id]), [Total Hours Worked])

Peer Hours Worked = SUMX(VALUES('Table'[Case id]), [Peer Hours Worked])

Change the measures put in the last two columns of the matrix to these two measures and check Total again to see if it is correct.

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

[harmishPatel]

@Anonymous Thanks sir!

[Greg_Deckler]

@harmishPatel Might be able to get there with a disconnected table approach. The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

[harmishPatel]

Thanks @Greg_Deckler  for prompt response!
I checked but find bit complex, chould you please help more in this?
Thanks in advance! 

[Greg_Deckler]

@harmishPatel It is complex unfortunately but not sure if I know of a different way to do it. Table and matrix visuals are extremely limited in their flexibility. Hard to help without a sample PBIX or sample source data.