Forum Discussion

EstherBR's avatar
EstherBR
Helper III
10 months ago
Solved

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...
  • GeraldGEmerick's avatar
    GeraldGEmerick
    10 months ago

    EstherBR Here is an example that more or less aligns with what you are trying to do:

     

    Create a table called "Categories":

    CategorySort
    Total Segment1
    Dallas2
    New York3
    San Francisco4

     

    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 _Return

     

    Put the Category column from your Categories table as the rows to your matrix and My Measure as the Values.