Forum Discussion
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
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Post PatronHi 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.
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
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....).