Calculate Rows in a Customized Matrix

[Anonymous]

Hi, 

I'm generating a matrix using all customized columns , one column is one measure. And I take rows headers as Department. 

Usually , I can do some measure between columns based on these departments. But how can I mwasure between rows based on each columns (each measure).

I want to add some calculated rows like measure1 = total - Dept. 1

Hope anyone can help here. 

Thanks!

[v-juanli-msft]

Hi @Anonymous 

It doesn't support to add measures/columns in rows the same as in columns.

You could change the "Total" field per your requirement.

For example, to change the total to show the totals except the "Dept.1",

Create a measure as below

Measure1 hasonevalue =
IF (
HASONEVALUE ( 'Table'[Department] ),
'Table'[Actual 2018],
SUMX (
FILTER ( 'Table', 'Table'[Department] <> "Dept.1" ),
'Table'[Actual 2018]
)
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft 

Thanks for your suggestion. I know it can be displayed to have a new column with the required total. But the values listed are duplicated except the total. it's really not a healthy report.

What I want to achieve is to have two total value rows in the bottom of the table. one is total, another is total without Dept.1.

I tried to group all except Dept. 1 to have a subtotal without Dept. 1 , but the thing is I have two levels of group already. If the group level is deep , the structure of data report would not be friendly displaying - refer to below screen. The single would also be grouped with several levels. 

So I'm curious about how we can make the report as two rows in the bottom , one is total , another is total without Dept. 1.