cancel
Showing results for
Did you mean:
Frequent Visitor

## Create a Martrix which sum several row in a table

 Date Main Value Assist 1 Value Assist 2 Value 01/04/2017 A £  500.00 B £  100.00 C £  100.00 01/03/2017 B £  600.00 B £  200.00 C £  100.00 01/08/2017 C £  700.00 C £  300.00 C £  100.00

I want to create a Matrix with the row showing the month and the column showing A/B/C.

The value would be the sum of value in the three 'value' column.

Any know the DAX function that I could use to construct the matrix that I want? Many Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Create a Martrix which sum several row in a table

I did it the following way.

1. Create a summarized table called MainValues -  SUMMARIZE(MultiRow,,MultiRow[MainValue]).

This will create unique MainValue from the factable - MultiRow and will act as a dimension table.

2.  Using Manage Relationship create relationship between MainValues and MultiRow

a) on Columns MainValue from both the tables

b) on column MainValue from MainValue and MultiRow[Assist1]

c)  on column MainValue from MainValue and MultiRow[Assist2]

Note only (a) will be active and others will not  be active.

3.  Create measures

a)  SumTot1 = CALCULATE(SUM(MultiRow[Value]))

b) SumTot2 = CALCULATE(SUM(MultiRow[Value2]),USERELATIONSHIP(MainValue[MainValue],MultiRow[Assist1]))

c)  SumTot3 = CALCULATE(SUM(MultiRow[Value3]),USERELATIONSHIP(MainValue[MainValue],MultiRow[Assist2]))

d)  SumTot = [SumTot1] + [SumTot2] + [SumTot3]

4. Now create a matrix table using

a)  MainValue [MainValue] as Columns

b)  MultiRow[Date] as Rows

c)  [SumTot] as  Values.

Note : replace MutilRow table with your Facttable name and Value, Value2, Value3 columns with your fact table column names.

Sample output

If this works for you please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
7 REPLIES 7
Member

## Re: Create a Martrix which sum several row in a table

Hi @kenbie0126,

You can use matrix visual and play with the numbers.

Could you please form result that you want to see I'll try to help.

Frequent Visitor

## Re: Create a Martrix which sum several row in a table

It would look like this but the result I am looking for is:

 A B C 01/04/2017 £  500.00 £  100.00 £     100.00 01/03/2017 £           - £  800.00 £     100.00 01/08/2017 £           - £           - £  1,100.00
Member

## Re: Create a Martrix which sum several row in a table

In this case you need 2 columns (one for ABC , second for values. Or measure which will calculate your different columns for values as 1.

Frequent Visitor

## Re: Create a Martrix which sum several row in a table

The format cannot be changed as it related to several calculations behind. I have been struggling to transfer the format as one column ABC and the next one as the value.

However, the Value split into A/B/C could be based on a certain percentage split of a sum for the month.

For example, we have products to sell and the comission split to salesperson A, B and C. They could also get percentage of sales due t passing a customers/ assist in sales. Any suggestions?

Many thanks!

Member

## Re: Create a Martrix which sum several row in a table

In this case you need to have value as 1 column, you can't build any logic based on 3 values based on 3 columns. Or change your logic for data, better will be to have 1 column for (main, assist.....) second for (a,b,c....) third for (value1,2,3....).

Highlighted
Community Support Team

## Re: Create a Martrix which sum several row in a table

Hi @kenbie0126,

As bsas said, based on current table structure, 'you can't build any logic based on 3 values based on 3 columns.'

Since 'The format cannot be changed as it related to several calculations behind.', you can keep its original format and create a new calculated table. Then, drag corresponding fields from new table.

```Month-Value2 =
UNION (
SELECTCOLUMNS (
'Month-Value',
"Date", 'Month-Value'[Date ],
"Cat", 'Month-Value'[Main],
"Val", 'Month-Value'[Value]
),
SELECTCOLUMNS (
'Month-Value',
"Date", 'Month-Value'[Date ],
"Cat", 'Month-Value'[Assist 1],
"Val", 'Month-Value'[Value_1]
),
SELECTCOLUMNS (
'Month-Value',
"Date", 'Month-Value'[Date ],
"Cat", 'Month-Value'[Assist 2],
"Val", 'Month-Value'[Value_2]
)
)```

Best regards,
Yuliana Gu

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

## Re: Create a Martrix which sum several row in a table

I did it the following way.

1. Create a summarized table called MainValues -  SUMMARIZE(MultiRow,,MultiRow[MainValue]).

This will create unique MainValue from the factable - MultiRow and will act as a dimension table.

2.  Using Manage Relationship create relationship between MainValues and MultiRow

a) on Columns MainValue from both the tables

b) on column MainValue from MainValue and MultiRow[Assist1]

c)  on column MainValue from MainValue and MultiRow[Assist2]

Note only (a) will be active and others will not  be active.

3.  Create measures

a)  SumTot1 = CALCULATE(SUM(MultiRow[Value]))

b) SumTot2 = CALCULATE(SUM(MultiRow[Value2]),USERELATIONSHIP(MainValue[MainValue],MultiRow[Assist1]))

c)  SumTot3 = CALCULATE(SUM(MultiRow[Value3]),USERELATIONSHIP(MainValue[MainValue],MultiRow[Assist2]))

d)  SumTot = [SumTot1] + [SumTot2] + [SumTot3]

4. Now create a matrix table using

a)  MainValue [MainValue] as Columns

b)  MultiRow[Date] as Rows

c)  [SumTot] as  Values.

Note : replace MutilRow table with your Facttable name and Value, Value2, Value3 columns with your fact table column names.

Sample output

If this works for you please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!