Forum Discussion
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.
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
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
7 Replies
v-yulgu-msftMicrosoft 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] ) )
Best regards,
Yuliana Gu
bsasPost 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.
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 - bsas
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.