Dynamic Column Name in Matrix base on a measure name

[dgibson]

I have a set of data that uses measures that I want to group into sections in a Matrix.

The source data looks like:

  Location

  CurrentYearSales

  Prior Year Sales

  CurrentYearGuestCount

  PriorYearGuest Count

I have 2 measures  

  1) CompSalesPercent = (currentYearSales -PriorYearSales) / PriorYearSales

  2) CompGuestPerenct = (currentYearGuest - priorYearGuest) / priorYearGuest

I want to build a a measure based on the measure name that will be a heading column in the Matrix (Sales and Traffic) to group the data together.

It would look like:

                 Sales                            Traffic

location    CompSalesPercent       CompGuestPercent

1                 3.4                                 2.1

2                 2.8                                2.6

Is there any way to build a measure or column based on the name of the Measure

Thanks

David

[v-yulgu-msft]

Hi @dgibson,

For measures, it is not possible to add a group header based on measures in Matrix.

As a workaround, you should get the CompSalesPercent and CompGuestPerenct values via calculated columns. In this scenario, please generate a calculated table like below:

Table 2 =
UNION (
    SELECTCOLUMNS (
        'Table 1',
        "Location", 'Table 1'[Location],
        "Type", "Sales",
        "Name", "CompSalesPercent",
        "Value", ( 'Table 1'[CurrentYearSales] - 'Table 1'[PriorYearSales] )
            / 'Table 1'[PriorYearSales]
    ),
    SELECTCOLUMNS (
        'Table 1',
        "Location", 'Table 1'[Location],
        "Type", "Traffic",
        "Name", "CompGuestPercent",
        "Value", ( 'Table 1'[CurrentTearGuestCount] - 'Table 1'[PriorYearGuestCount] )
            / 'Table 1'[PriorYearGuestCount]
    )
)

Use a matrix to place fields from above table. Drill down to the bottom level.

Best regards,

Yuliana Gu

[dgibson]

This only works if you dont have rollups at the row level, where the values raw values are need to calculate the measure.  Ie.  2 locations that have compSales of 1% do not rollup to comp Sales of 2%.  I was trying to avoid this, because this is a large dataset with multiple measures (example was simplified) and will need to load the the low level multiple times for each 'Type' in your example.

Thanks

[dgibson]

I have a set of data that uses measures that I want to group into sections in a Matrix.

The source data looks like:

  Location

  CurrentYearSales

  Prior Year Sales

  CurrentYearGuestCount

  PriorYearGuest Count

I have 2 measures  

  1) CompSalesPercent = (currentYearSales -PriorYearSales) / PriorYearSales

  2) CompGuestPerenct = (currentYearGuest - priorYearGuest) / priorYearGuest

I want to build a a measure based on the measure name that will be a heading column in the Matrix (Sales and Traffic) to group the data together.

It would look like:

                 Sales                            Traffic

location    CompSalesPercent       CompGuestPercent

1                 3.4                                 2.1

2                 2.8                                2.6

Is there any way to build a measure or column based on the name of the Measure

Thanks

David