Forum Discussion
Matrix - Add a Row from another table
Hi,
I have the following matrix:
with the following data model:
The customer wants to add in the first row of the matrix the total sum of sales of Segment. The matrix should look like this:
Is it possible to do it? I can change the data model if necessary.
Thank you so much!
EstherBR Here is an example that more or less aligns with what you are trying to do:
Create a table called "Categories":
Category Sort Total Segment 1 Dallas 2 New York 3 San Francisco 4 Set the Sort column to be the Sort by column for Category.
Now create the following measure for use in your Matrix:
My Measure = VAR _Category = MAX( 'Categories'[Category] ) VAR _Return = IF( _Category = "Total Segment", SUM( 'Cities'[Sales] ) CALCULATE( SUM( 'Cities'[Sales] ), FILTER( 'Fact Table', [City] = _Category ) ) ) RETURN _ReturnPut the Category column from your Categories table as the rows to your matrix and My Measure as the Values.
6 Replies
EstherBR You will need to use a table for the rows that does not have any relationships to your other tables. You can then construct a single measure that forms the relationship between that table and the rest of your data that you would then use in the matrix as the Value.
Thanks for your answer GeraldGEmerick , It is not clear to me. Could you provide an example?
EstherBR Here is an example that more or less aligns with what you are trying to do:
Create a table called "Categories":
Category Sort Total Segment 1 Dallas 2 New York 3 San Francisco 4 Set the Sort column to be the Sort by column for Category.
Now create the following measure for use in your Matrix:
My Measure = VAR _Category = MAX( 'Categories'[Category] ) VAR _Return = IF( _Category = "Total Segment", SUM( 'Cities'[Sales] ) CALCULATE( SUM( 'Cities'[Sales] ), FILTER( 'Fact Table', [City] = _Category ) ) ) RETURN _ReturnPut the Category column from your Categories table as the rows to your matrix and My Measure as the Values.
