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