Forum Discussion

ShNBl84's avatar
ShNBl84
Advocate II
6 years ago
Solved

Use string value from table as a formula

We are trying to calculate the average price of an item over several months. The total price is the ingredient price plus the labor price. The labor price could change monthly. We want to make a mea...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi ShNBl84 ,

    Unfortunately, it is impossible to use a string value as a formula to use directly in Power BI. Maybe you could try the Tabular Editor 3rd party tool as natelpeterson suggested.

    I think you could import all the data and create formulas to implement in Desktop. I create a sample to explain what I mean.

     

    Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Ingredient price] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Month] ) = 9, ( a / 0.975 ) + 10,
        MAX ( 'Table'[Month] ) = 10, ( a / 0.975 ) + 11,
        SUM ( 'Table'[Ingredient price] )
    )

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi ShNBl84 ,

Unfortunately, it is impossible to use a string value as a formula to use directly in Power BI. Maybe you could try the Tabular Editor 3rd party tool as natelpeterson suggested.

I think you could import all the data and create formulas to implement in Desktop. I create a sample to explain what I mean.

 

Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Ingredient price] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Month] ) = 9, ( a / 0.975 ) + 10,
        MAX ( 'Table'[Month] ) = 10, ( a / 0.975 ) + 11,
        SUM ( 'Table'[Ingredient price] )
    )

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.