We are creating a farm finance report for farmers based on data recorded on a farm management software. I am trying to create a forecast scenario for Price and Yield as seen in the image.
The numbers entered in these forecast scenarios are then used to predict other financial mesures as shown by the arrows
The issue is that every crop has different harvest weight units which would require a different series of numbers for yield forecast .For eg bushels (bu) would be generateseries(0,300,0.1), whereas bales would be generateseries(0,30,1). I tried to create a large series of (0,500,0.1) to accomodate for the different units but because of the amount of rows that this tabele creates power bi doesn’t give accurate results when we drill into decimals. If I type in 120.00 it automatically redirects it to 119.9 Now while this may not matter in a different situation the decimals do matter in yield and so they need to be accurate.
My question here is, is there a way to dynamically change the series of the what if parameter by selecting a yield unit from say a slicer . Or is there another way to handle this query?
Any help is much appreciated
@Nicola
Hope you don’t mind, but I took a look at this. Here’s what I got. Start with a table in Power Query that has the Crop Name, Start, End, and Increment for the list:
Then I created function that will use those input to create a list.
(Start,End,Increment)=>
let
List =
List.Generate(
()=> Start
,each _<=End
,each _ +Increment
)
in
List
Back in the table from above, you want to add a custom column by invoking the function:
This will give you a new column that just says “List”. You want to expand that column and you will see all the values that were generated. Remove the other columns and just leave Crop and this new column. Be sure to set data types:
I’d imagine there would be some miscellaneous things that would need to be taken of. Like what happens when you change the crop slicer with a value selected in the Values Slicer? Not sure. but that could be accounted. I believe the hard part is getting that slicer to change in the first place.
Hey Nick_M,
Thanks for taking the time to work on this. This looks like a viable solution . Let me try it out and see if it works.
This may be a stupid question , How do I get my slicer to just give me one box (an equal to) like it automatically generates for the what if parameters. The current options are “less than or equal to”, “greater than or equal to” and between which gives me two boxes. I would prefer to just have one box showing to avoid confusion as the dropdown or list options are not very user friendly if you’ve got to scroll through a lot of numbers. (Numeric values in slicer doesn’t give you the search option that text inputs in slicer’s give)
