Forum Discussion

acanepa's avatar
acanepa
Resolver I
8 years ago
Solved

[DAX] Dynamic Table and Summarize Table

Hello,   I'm using the following formula for generating a dynamic measure, creating a table (Slicer Table) on the side with each id and measure name. I have done this several times and works great....
Need Help
  • acanepa's avatar
    acanepa
    8 years ago

    Hi v-jiascu-msft,

     

    As I posted earlier my problem was around to generate a dynamic measure inside a table.

    The solution I found rely on making a SWITCH operator with a generation of a table in each measure.

    I was trying to calculate the average YOY for 2017*. Because is a measure is necessary to create a table to eliminate "row context".

     

    The following calculation relies on Slicer table with the measures Traffic, Clicks and Users. The beauty of this you will need only one measure for display all measures at the same time and all measures share the same format. 

     

    Calculation=

SWITCH(TRUE,
max(id)=1,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Traffic]),
[yoy]),
max(id)=2,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Clicks]),
[yoy]),
max(id)=3,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Users),
[yoy]),
BLANK()
)

    * YOY means from a given month in 2017, compared with the same month 2016, i.e. Jan-2016 vs Jan-2017.

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi acanepa,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

  • acanepa's avatar
    acanepa
    Resolver I
    8 years ago

    Hi v-jiascu-msft,

     

    As I posted earlier my problem was around to generate a dynamic measure inside a table.

    The solution I found rely on making a SWITCH operator with a generation of a table in each measure.

    I was trying to calculate the average YOY for 2017*. Because is a measure is necessary to create a table to eliminate "row context".

     

    The following calculation relies on Slicer table with the measures Traffic, Clicks and Users. The beauty of this you will need only one measure for display all measures at the same time and all measures share the same format. 

     

    Calculation=

SWITCH(TRUE,
max(id)=1,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Traffic]),
[yoy]),
max(id)=2,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Clicks]),
[yoy]),
max(id)=3,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Users),
[yoy]),
BLANK()
)

    * YOY means from a given month in 2017, compared with the same month 2016, i.e. Jan-2016 vs Jan-2017.

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Thanks for sharing with the Community.

       

      Best Regards,

      Dale