Forum Discussion

ShNBl84's avatar
ShNBl84
Icon for Advocate II rankAdvocate 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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

ShNBl84  - The Tabular Editor 3rd party tool could be useful. There are scripts and strategies to easily create many measures. However, I'd recommend reviewing what you're trying to accomplish here.

 

An alternative is to have a table of labor prices, which could be consumed by the measure - then you're not maintaining many measures, but rather one which pulls in the relevant values. Perhaps we can further explore what's needed here?

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson