Use string value from table as a formula

[ShNBl84]

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 measure that shows the average price based on the selected months.

An example of a pricing formula is:

May 2019: (Ingredient price / .975) + 10

Instead of writing a new measure for every item and every month, we are experimenting with putting a DAX formula into Excel and inserting it as a measure. For example:

Item	Date	Formula
Product 1	May 2019	=(SELECTEDVALUE([Ingredient price]) / .975) +10
Product 1	June 2019	=(SELECTEDVALUE([Ingredient price]) / .975) +11

Is there a way to pull a string value (such as those under Formula) and use them as formulas in Power BI?

Do you have a better solution?

Thanks.

[v-xuding-msft · Accepted solution]

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]

@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.