Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic field names in mesures

Let’s say I’ve below areas as a table value. Area USA UK AUS CAN   I’ve created a measure for selected item from the Area table. (It’s always one selected in slicer). ...
Need Help
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Sorry for our delay in response, but it does not support to calculate dynamic formula/field in measure, but we can use "unpivot" in power query editor to meet your requirement:

     

     

     

    If you do not want to change the construction of data, we can use switch to calculate for each area:

     

     

    Sales =
SUMX (
    DISTINCT ( Country[Country] ),
    SWITCH (
        [Country],
        "USA", SUM ( Sales[Sales_USA] ),
        "BRA", SUM ( Sales[Sales_BRA] ),
        "CAN", SUM ( Sales[Sales_CAN] ),
        "JPN", SUM ( Sales[Sales_JPN] )
    )
)

     


    By the way, PBIX file as attached.


    Best regards,

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

Your data is not well arranged.  You should unpivot your data to have all Countries appear in 1 column only and in another column you have have the sales figures.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ashish_Mathur , I took the the above data just an example to explain the scenario but in reality the data and the usecase is different and we can't change the data foramt for various reasons. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I've created a measure as below.

      Sales =
      var SelectedCountry = SELECTEDVALUE(Country[Country])
      var Exp = "Sum(Sales[Sales_"&SelectedCountry&"])" // this is returning Sum(Sales_USA)
      return CALCULATE(Exp) //here the return statement not evaluating the Exp variable instead it's just dislaying Sum(Sales_USA)
       
      Is there anyway to evaluate the variable inside calculate?
      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for our delay in response, but it does not support to calculate dynamic formula/field in measure, but we can use "unpivot" in power query editor to meet your requirement:

         

         

         

        If you do not want to change the construction of data, we can use switch to calculate for each area:

         

         

        Sales =
SUMX (
    DISTINCT ( Country[Country] ),
    SWITCH (
        [Country],
        "USA", SUM ( Sales[Sales_USA] ),
        "BRA", SUM ( Sales[Sales_BRA] ),
        "CAN", SUM ( Sales[Sales_CAN] ),
        "JPN", SUM ( Sales[Sales_JPN] )
    )
)

         


        By the way, PBIX file as attached.


        Best regards,