Forum Discussion
Using Parameter to Divide
Hello
I have some raw data sent to me where there are no date fields. It has Country Code and Total Dollars. The person sending me the data will tell me the number of months the data is representing. It could be anywhere from one month to 24 months of data. I don't need to create a new date field, but I would like to create a Parameter or filter that divides the total by the number of months.
So, if I create a table showing total dollars by Country Code, with the totals at the bottom, by applying the Parameter or Filter called number of months, let's say in this example it is "6", the row totals and grand total for Total Dollars will be divided by six.
Are you wanting to do this as a filter in the report or the Power Query stage? If in the report you'll need to use a MEASURE to be able to reference the SELECTEDVALUE of the slicer, but I feel like this might be better done in the Power Query stage?
Here's the Measure solution:
Total Dollars MEASURE = DIVIDE( SUM(Table[Total Dollars]), SELECTEDVALUE(Parameter[NumberMonths], 1))
How often are you sent this data and is it being combined into one report?
Have you worked with Parameters in Power Query before? Here's an intro you can follow along with on how they work,
Use Parameters to Combine Data
Your requirement is slightly different, but same concept. Depending on how you combine and work with this data, the exact solution will vary so if you can provide a bit more info we can provide a better solution.
2 Replies
Are you wanting to do this as a filter in the report or the Power Query stage? If in the report you'll need to use a MEASURE to be able to reference the SELECTEDVALUE of the slicer, but I feel like this might be better done in the Power Query stage?
Here's the Measure solution:
Total Dollars MEASURE = DIVIDE( SUM(Table[Total Dollars]), SELECTEDVALUE(Parameter[NumberMonths], 1))
How often are you sent this data and is it being combined into one report?
Have you worked with Parameters in Power Query before? Here's an intro you can follow along with on how they work,
Use Parameters to Combine Data
Your requirement is slightly different, but same concept. Depending on how you combine and work with this data, the exact solution will vary so if you can provide a bit more info we can provide a better solution.