Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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, we...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    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, and Timeframe — 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.