Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
@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?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |