Forum Discussion

harmishPatel's avatar
harmishPatel
Frequent Visitor
2 years ago
Solved

Matrix Visual Total Row

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.


Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      harmishPatel
      Frequent Visitor
      2 years ago

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

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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.