How to get total number in power bi matrix rows

[wulawula]

Here's a sample of raw data in the Power BI table. 

Col A	Col B	Col C	Col D	Col E
AA	BCD	2019-Q3	12	91%
AA	BCD	2019-Q4	37	91%
AA	BCD	2020-Q1	46	86%
AA	BCD	2020-Q2	60	76%
AA	EF	2019-Q3	12	87%
AA	EF	2019-Q4	39	87%
AA	EF	2020-Q1	48	99%
AA	EF	2020-Q2	49	97%
AA	GH	2019-Q3	12	96%
AA	GH	2019-Q4	39	97%
AA	GH	2020-Q1	48	100%
AA	GH	2020-Q2	49	100%

I want to sum the numbers of Col D group by Col B and Col C and list them as an aggregate number in matrix rows. How can I get the desire layout like this? It seems easy but couldn't figure it out. Thank you in advance!

Col A	Col B	Col D	2019-Q3	2019-Q4	2020-Q1	2020-Q2
AA	BCD	155	91%	91%	86%	76%
	EF	148	87%	87%	99%	97%
	GH	148	96%	97%	100%	100%

Sorry I forgot to mention Col C is a growing list, like it will show 2020-Q3, 2020-Q4... and also will show previous quarters like 2019-Q2. The Col D sum numbers is the result filtered from the slicer outside the matrix.

[PaulDBrown · Accepted solution]

@wulawula 

Alternatively, if you want the DIM period table to also filter the columns on the converted matrix, you can try the following measure to use in the filter panel for the Matrix:

FILTER Matrix columns = 
VAR dim = VALUES('Dim Period'[AppCol])
VAR clod = CALCULATETABLE(VALUES('Matrix Columns'[MtrxCol]), 'Matrix Columns'[MtrxCol] IN {"ColD"})
VAR REF = UNION(dim,clod)
VAR matrix = VALUES('Matrix Columns'[MtrxCol])
RETURN
COUNTROWS(INTERSECT(REF,matrix))

And you get this (I've also altered the "ColD" measures to give you the total for the column in the matrix):
No filters for period

Filters applied to DIM period:

New PBIX file attached

[PaulDBrown]

@wulawula 

You can achieve this by using a disconnected "Matrix Columns" table, which basically includes the column header "ColD" & the values for the periods (created in Power Query by isolating column header "ColD", creating a DIM table for the periods and appending both - index added for sorting purposes). Since the table is created by appending values from the dataset, it will grow accordingly:

The model looks like this:

Create standard measures (SUM) for values for ColD and ColE form the original fact table.

Sum of Values ColD = SUM('Orig Fact'[Col D])

Sum of Period Values = SUM('Orig Fact'[Col E])

Next create  measures to identify which of the above to use in the filter context of the columns in the matrix:

Sel Period Values = CALCULATE([Sum of Period Values], TREATAS(VALUES('Matrix Columns'[AppCol]), 'Orig Fact'[Col C]))

Sel Value ColD = CALCULATE([Sum of Values ColD], ALLSELECTED('Dim Period'[AppCol]))

and 

Selected Value = IF(
SELECTEDVALUE('Matrix Columns'[AppCol]) = "ColD", FORMAT([Sel Value ColD], "###"), 
FORMAT([Sel Period Values], "##%"))

 Add the ColA and ColB to a matrix as rows; add the "Matrix columns" as Columns and [Selected Value] as values and you get this:

I've included the PBIX file for your reference

[wulawula]

Thank you so much for the help! It seems a good solution for the case. I'll try this out!

[Greg_Deckler]

@wulawula Yeah, so you are looking to add an "extra" total column to your matrix. Turns out that is not actually so easy, but there is a way to do it.

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

[wulawula]

Thank you Greg! I'm sorry forgot mention the quarters are based on the filtered result from the outside slicers.

Yes, it's not easy as I thought. I thought it could be resolved by a calculated column like NewColumn = CALCULATE( SUM('Table'[COL D]) , ALLEXCEPT('Table', 'Table'[COL A],'Table'[COL B],'Table'[COL C] )) to solve the problem. But it doesn't work. Col A will be thousands of names there. So it would be a long list according to your suggestion and has to be a dynamic one. Not sure if that would apply to my case. Still thinking if there's a better way to resolve this. Thank you so much!

[vivran22]

@wulawula 

I am not sure if you have considered this:

One alternative is to pivot the Col C with values from Col E

The trade-off would be that we need to manually add the newly included quarter in the matrix:

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter