Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
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
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 |
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
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
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....).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |