Dynamic Measure Names

[BSM1985]

Hi Team,

Below is the matrix. I would like to get the dynamic measures which is depending upon the the field year.

Ex: I want to create a calculation % Sales Vs 2018 where the formula is (2019 Sales-2018 Sales)/2019 Sales. This can be done with DAX. My requirement is the name of the measures should be dynamic such as % Sales Vs 2018, % Sales Vs 2017 etc.,  I added the current output vs expected output below. I also attached the pbix file. https://1drv.ms/u/s!Ao1Y41keMwfAcgZ9w3oyHU_NiOA 

I also highlighted the calculations in RED in below snapshot.

Could you help me on this.

 

[MFelix · Accepted solution]

Hi @BSM1985 ,

You cannot directly make the change of a measure name in any visualizaiton however thinking outside the box this can be possible, what I di was the following:

• Created a Date table (in case of the use of dates is a best practice)
• On that table created the matrix and matrix sort column has you have
• Created a tabled with the years and the Sales profit row, also added a column with the text of sales variation:

Sales Measure Table = UNION(DISTINCT(DateDim[Year]) , ROW("Matrix", "Sales"), ROW("Matrix", "Profit"))

• Added a column to this table with the following syntax:

DinamicName = IF('Sales Measure Table'[Year] in {"Sales", "Profit"}, 'Sales Measure Table'[Year],"Sales % vs " & CONVERT('Sales Measure Table'[Year], INTEGER) - 1)

• Created the following measures:

Variatation vs py = 
VAR Total_sales =
    SUM ( Orders[Sales] )
VAR Previous_year =
    CALCULATE (
        SUM ( Orders[Sales] ),
        FILTER ( ALL ( DateDim ), DateDim[Year] = MAX ( DateDim[Year] ) - 1 )
    )
VAR result =
    DIVIDE ( ( Total_sales - Previous_year ), Total_sales )
RETURN
    result


MatrixValues =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Sales Measure Table'[Year] ) = "Sales", SUM ( Orders[Sales] ),
    SELECTEDVALUE ( 'Sales Measure Table'[Year] ) = "Profit", SUM ( Orders[Profit] ),
    SELECTEDVALUE ( 'Sales Measure Table'[Year] )
        = FORMAT ( SELECTEDVALUE ( DateDim[YearMatrix] ), "#" ),
        IF (
            [Variatation vs py] = BLANK ()
                || SELECTEDVALUE ( DateDim[YearMatrix] )
                    = MINX ( ALL ( DateDim[YearMatrix] ), DateDim[YearMatrix] ),
            BLANK (),
            FORMAT ( [Variatation vs py], "#.00%" )
        )
)

• Now setup your matrix in the following way:
  • Rows:
    • Region
    • State
  • Columns
    • YearMatrix
    • DinamicName
  • Values
    • MatrixValue

Result below and in attach PBIX file:

[Anonymous]

The solution you presented is awsome. 

I would like to ask if is possible to sort the table by one of the measures, like "Variatation vs 2018"? 
I have tried, but i can't find a way to do this. 

[BSM1985]

Hello @MFelix 

Thank you very much for the solution