Clustered Bar Chart - How to visualize both static data and calculated data together?

[sharkrocket]

Hello friends. Thank you for taking the time to help me.

Context: I have data from last year. There are 3 values I'm tracking (Denominator, % Met Progress, and % Complete) - I know the values, and they are static, so I have those values in a table. I also have a 50,000 row spreadsheet for the current year from which I need to calculate the 3 values (Denominator, % Met Progress, and % Complete). 

Task: I want to compare this year's results to last year's results in a clustered bar chart. 

Question: How would you suggest I tie the static data from last year together with the calculated data from this year so that they can be visualized on the same clustered bar chart? 

What I've tried: I created a measure for the 3 values for the current year, and I combined those measures along with the static values from last year in a calculated table. I used the calculated table for a clustered bar chart. The issue is that since a calculated table is static, any filters/slicers that I try to use on the report do not affect the chart. I need to be able to filter down this year's data in the visual.

[MohamedFowzan1]

Hi @sharkrocket 

You could simply have a Disconnected table and handle with the DAX measure

Your Disconnected table could look like this:

Metric	Year
Denominator	Last Year
% Met Progress	Last Year
% Complete	Last Year
Denominator	This Year
% Met Progress	This Year
% Complete	This Year

Have a measure like this:

UnifiedMetricValue =
SWITCH(
    TRUE(),
    SELECTEDVALUE(Category[Metric]) = "Denominator" && SELECTEDVALUE(Category[Year]) = "Last Year", 12345,  -- static value
    SELECTEDVALUE(Category[Metric]) = "% Met Progress" && SELECTEDVALUE(Category[Year]) = "Last Year", 0.76, -- static value
    SELECTEDVALUE(Category[Metric]) = "% Complete" && SELECTEDVALUE(Category[Year]) = "Last Year", 0.45,     -- static value

    SELECTEDVALUE(Category[Metric]) = "Denominator" && SELECTEDVALUE(Category[Year]) = "This Year", [Denominator Measure],
    SELECTEDVALUE(Category[Metric]) = "% Met Progress" && SELECTEDVALUE(Category[Year]) = "This Year", [% Met Progress Measure],
    SELECTEDVALUE(Category[Metric]) = "% Complete" && SELECTEDVALUE(Category[Year]) = "This Year", [% Complete Measure]
)

 
In place of static value you could also refer to the column or table where this is instead or even use lookup if the metric is present in both the table depending on how your table looks

Example using Lookup:

UnifiedMetricValue =
VAR Metric = SELECTEDVALUE(Category[Metric])
VAR Year = SELECTEDVALUE(Category[Year])
RETURN
SWITCH(
    TRUE(),
    Year = "Last Year", 
        LOOKUPVALUE(LastYearMetrics[Value], LastYearMetrics[Metric], Metric),

    Year = "This Year" && Metric = "Denominator", [CY_Denominator],
    Year = "This Year" && Metric = "% Met Progress", [CY_MetProgress],
    Year = "This Year" && Metric = "% Complete", [CY_Complete]
)

[sharkrocket]

Thank you for your reply. I think that's pretty similar to what I did. My code was:

Table = {("Denominator", 2024, [2024Denominator]), ("Denominator", 2025, [2025_Denominator]), ("MetProgress", 2024, [2024MetProgress]), ("MetProgress", 2025, [2025_MetProgress]), ("Complete", 2024, [2024Complete]), ("Complete", 2025, [2025_Complete])}.

My issue is that when I use this table in a clustered bar chart, the 2025 data is not affected by slicers (I would not expect 2024 data to be affected by slicers anyways since those are static values). 

Is there anything different about the way you suggested I build the table that would make it act differently? 

[MohamedFowzan1]

• Replace your calculated/static table with a pure disconnected “axis”/“selection” table containing only Metric and Year (NO data values).

Calculated table will not act dynamic so you will only need to have a helper table with the combination of metric and year and not the actual values of 2025. In the suggestion above, I request using the measure defined and calculating 2025 data in real time which will make it dynamic and get reflected changing based on the slicer selection. Make sure to not include the data into the calculated table. Let me know if you need further clarification or provide sample data for further analysis.

[sharkrocket]

Thank you, @MohamedFowzan1 . I will try that solution and try to check back in if it works. 

[Tutu_in_YYC]

"any filters/slicers that I try to use on the report do not affect"..

If you want the last years data to be sliced-able, you must have the data in the same granularity as this years data, data. And the dimensions that you use to filter and slice must have relationships to the previous years data table.

[sharkrocket]

I do know that slicers will not affect the last year's data since it is static, but more precisely, I need to be able to filter the current year data for the visual, and my method of putting the static and calculated data together in one table results in data that is not filterable in report view.