Forum Discussion
How does one setup a single visual that can display various timeframes, versions and values?
I would like to create a single visual and have slicers that will allow the user to change the version (i.e. forecast, budget, prior year), change the timeframe being displayed (i.e. previous day, week to date, month to date, year to date) and also change the values being displayed (i.e. sales dollars, gross margin dollars, units).
Sample:
Not sure if this can be done with calculation groups and/or field parameters.
Thank you for you assistance.
Hello Anonymous,
Thanks for your update.To display and dynamically switch three measures (e.g., Sales, $ Variance, % Variance) in a single visual using the hierarchical field parameter approach, you can extend the solution by creating multiple field parameters or enhancing the existing parameter table. Below approach might help you:
- Add rows to the parameter table to include all combinations for the three measures (Sales, $ Variance, % Variance) across Versions (Actual, Forecast, Budget) and Timeframes (MTD, YTD, WTD).
- Create separate field parameters for each measure type (Sales, $ Variance, % Variance). Each parameter table would contain the combinations of Versions and Timeframes for that measure. Link these parameters to the visual (e.g., a matrix with three columns).
- Check each measure's DAX definition accounts for the selected timeframe using time intelligence functions (e.g., TOTALYTD, DATESMTD) and calculates variances correctly (e.g., [Forecast] - [Actual] for $ Variance, ([Forecast] - [Actual]) / [Actual] for % Variance).
This setup will reflect your example (e.g., Market1 showing Sales, $ Variance, % Variance) and allow dynamic switching.
I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.
9 Replies
Hi Anonymous
Yes, what you're trying to achieve is absolutely possible using either Calculation Groups or Field Parameters in Power BI.
Between the two, Field Parameters are much easier to implement, especially for visuals controlled by slicers.
-
For values like Sales $, GM$, and Units – you can create a field parameter with measures.
-
For dimensions like Version (Forecast, Budget, etc.) or Timeframe (WTD, MTD, etc.) – if these are separate columns in your model, you can also create field parameters with columns.
With field parameters, you can let the user switch between multiple measures or dimensions within a single visual, using slicers just like in your example.
For more information please refer to the linked video guides:
https://www.youtube.com/watch?v=V6WchPDZibI&t=2s&pp=ygUZZmllbGQgcGFyYW1ldGVycyBwb3dlciBiaQ%3D%3D
https://www.youtube.com/watch?v=nWgPynP9XDM&pp=ygUZZmllbGQgcGFyYW1ldGVycyBwb3dlciBiaQ%3D%3D
https://www.youtube.com/watch?v=-nqEv2YXLsU&t=18s&pp=ygUZZmllbGQgcGFyYW1ldGVycyBwb3dlciBiaQ%3D%3DIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
-
Rita,
Thank you for the prompt response and encouraging words that this is possible. However, I’m stuck. I’m sure I’m missing something obvious, but now does one make this work when the measures for Actual, Budget, etc. are already defined specifically for Sales$, Units, etc. Not sure how to get a generic measure that will allow me to control the values (sales $, units) and the version (budget, forecast, etc.) much less the timeframe variable. I’ve seen the videos you suggested but not seeing how to do what I outlined in my original posting. Any samples or other materials you would recommend?
Thank you,
Steve
Hi Anonymous
In order for this to work, your parameter table needs to include all possible combinations of the selections — for example:
"Sales – Actual – MTD", "Units – Forecast – YTD", etc. Each row in the table should represent a single valid combination of Measure Type, Version, and Timeframe, mapped to a specific pre-defined DAX measure.This is known as a hierarchical field parameter, and it allows the user to control three dimensions (Measure, Version, Timeframe) with a single slicer, rather than juggling multiple slicers and SWITCH logic.
For example :
Label Measure Measure Group Version Timeframe Sales $ - Actual - MTD [Sales_Actual_MTD] Sales Actual MTD Sales $ - Actual - YTD [Sales_Actual_YTD] Sales Actual YTD Sales $ - Actual - WTD [Sales_Actual_WTD] Sales Actual WTD Sales $ - Forecast - MTD [Sales_Forecast_MTD] Sales Forecast MTD Sales $ - Forecast - YTD [Sales_Forecast_YTD] Sales Forecast YTD Sales $ - Forecast - WTD [Sales_Forecast_WTD] Sales Forecast WTD Sales $ - Budget - MTD [Sales_Budget_MTD] Sales Budget MTD Sales $ - Budget - YTD [Sales_Budget_YTD] Sales Budget YTD Sales $ - Budget - WTD [Sales_Budget_WTD] Sales Budget WTD Units - Actual - MTD [Units_Actual_MTD] Units Actual MTD Units - Actual - YTD [Units_Actual_YTD] Units Actual YTD Units - Actual - WTD [Units_Actual_WTD] Units Actual WTD Units - Forecast - MTD [Units_Forecast_MTD] Units Forecast MTD Units - Forecast - YTD [Units_Forecast_YTD] Units Forecast YTD Units - Forecast - WTD [Units_Forecast_WTD] Units Forecast WTD Units - Budget - MTD [Units_Budget_MTD] Units Budget MTD Units - Budget - YTD [Units_Budget_YTD] Units Budget YTD Units - Budget - WTD [Units_Budget_WTD] Units Budget WTD GM$ - Actual - MTD [GM_Actual_MTD] GM$ Actual MTD GM$ - Actual - YTD [GM_Actual_YTD] GM$ Actual YTD GM$ - Actual - WTD [GM_Actual_WTD] GM$ Actual WTD GM$ - Forecast - MTD [GM_Forecast_MTD] GM$ Forecast MTD GM$ - Forecast - YTD [GM_Forecast_YTD] GM$ Forecast YTD GM$ - Forecast - WTD [GM_Forecast_WTD] GM$ Forecast WTD GM$ - Budget - MTD [GM_Budget_MTD] GM$ Budget MTD GM$ - Budget - YTD [GM_Budget_YTD] GM$ Budget YTD GM$ - Budget - WTD [GM_Budget_WTD] GM$ Budget WTD After you create the parameter table, add these calculated columns to classify your measures:
DAX
Measure Group = IF( SEARCH("Sales", 'Parameter Table'[Name], 1, 0) > 0, "Sales", IF( SEARCH("Units", 'Parameter Table'[Name], 1, 0) > 0, "Units", IF( SEARCH("GM$", 'Parameter Table'[Name], 1, 0) > 0, "GM$", "Other" ) ) ) Version = IF( SEARCH("Actual", 'Parameter Table'[Name], 1, 0) > 0, "Actual", IF( SEARCH("Forecast", 'Parameter Table'[Name], 1, 0) > 0, "Forecast", IF( SEARCH("Budget", 'Parameter Table'[Name], 1, 0) > 0, "Budget", "Other" ) ) ) Timeframe = IF( SEARCH("MTD", 'Parameter Table'[Name], 1, 0) > 0, "MTD", IF( SEARCH("YTD", 'Parameter Table'[Name], 1, 0) > 0, "YTD", IF( SEARCH("WTD", 'Parameter Table'[Name], 1, 0) > 0, "WTD", "Other" ) ) )You can now use any of these new columns —
Measure Group,Version, andTimeframe— as slicers in your report, to allow the user to filter or segment the parameter table based on each individual dimension.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi Anonymous,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thank you, Ritaf1983 for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.
Thank you.
Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Anonymous,
May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Appologies for the delay in responding. I was out on PTO and tied up with another project. This information is VERY helpful. This works great for changing a single value (e.g. Sales) on my visual. However, in my example I need to change three values (e.g. Sales, $ variance, % variance). How does one change three measures in the visual? Please revisit my example. Thanks again so much for the assistance give so far.
Hello Anonymous,
Thanks for your update.To display and dynamically switch three measures (e.g., Sales, $ Variance, % Variance) in a single visual using the hierarchical field parameter approach, you can extend the solution by creating multiple field parameters or enhancing the existing parameter table. Below approach might help you:
- Add rows to the parameter table to include all combinations for the three measures (Sales, $ Variance, % Variance) across Versions (Actual, Forecast, Budget) and Timeframes (MTD, YTD, WTD).
- Create separate field parameters for each measure type (Sales, $ Variance, % Variance). Each parameter table would contain the combinations of Versions and Timeframes for that measure. Link these parameters to the visual (e.g., a matrix with three columns).
- Check each measure's DAX definition accounts for the selected timeframe using time intelligence functions (e.g., TOTALYTD, DATESMTD) and calculates variances correctly (e.g., [Forecast] - [Actual] for $ Variance, ([Forecast] - [Actual]) / [Actual] for % Variance).
This setup will reflect your example (e.g., Market1 showing Sales, $ Variance, % Variance) and allow dynamic switching.
I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.Hi Anonymous,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.
