cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kenbie0126 Frequent Visitor
Frequent Visitor

Create a Martrix which sum several row in a table

 

 

Date MainValueAssist 1ValueAssist 2Value
01/04/2017A £  500.00B £  100.00C £  100.00
01/03/2017B £  600.00B £  200.00C £  100.00
01/08/2017C £  700.00C £  300.00C £  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
Super User

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

hi @kenbie0126

 

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

 

Capture.GIF

 

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
bsas Member
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.

kenbie0126 Frequent Visitor
Frequent Visitor

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

Untitled.jpg

 

 

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

 

 ABC
01/04/2017 £  500.00 £  100.00 £     100.00
01/03/2017 £           -   £  800.00 £     100.00
01/08/2017 £           -   £           -   £  1,100.00
bsas Member
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.

kenbie0126 Frequent Visitor
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! 

bsas Member
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
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]
    )
)

1.PNG

 

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
Super User

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

hi @kenbie0126

 

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

 

Capture.GIF

 

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!