Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kenbie0126
Regular 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

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!

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

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.
bsas
Post Patron
Post Patron

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.

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

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.

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! 

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!

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....).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.