Slicer to let users select which value is displayed on visualization

[modelmike]

I have a sales summary table that has value columns for actuals, forecast, budget. 

In a report, I am creating a time series line chart to visualize. I want to let users select whether to visualize actuals vs. budget OR actuals vs. forecast. So I was thinking to create a slicer that allows users to select which value fields are included on the chart, budget or forecast. I can't figure out a way to do this. Is it possible?

(I am a very novice Power BI user, this is my first week using the tool. Thanks for your patience  )

[v-yuta-msft]

Hi modelmike,

Slicer can only filter rows in your table so fields [actuals vs. budget] and [actuals vs. forecast] can't be sliced by a slicer in the chart. As a workaround, you can create two fields [Category] which contains values "actuals vs. budget" and "actuals vs. forecast" and [Values] which contains all values in [actuals vs. budget] and [actuals vs. forecast]. Then you can create a slicer based on [Category] field.

Best Regards,

Jimmy Tao

[modelmike · Accepted solution]

Thanks Jimmy. I found a way around this as well by creating a flat Union table. The Union table combines all like data-sets (actual, budget, forecast) and specifies "type" in a fixed column with all values in a value column. Then I can slice based on type.

This seems like a practical solution but I'm curious if this is a good or bad practice.

Union code:

Volume_Weeks = 
UNION (
    SELECTCOLUMNS (
        ACT_str_weeklysums,
        "Region", ACT_str_weeklysums[SLS_REGN_CD],
        "State", ACT_str_weeklysums[WSLR_ST_CD],
        "Wslr#", ACT_str_weeklysums[WSLR_NBR],
        "Channel", ACT_str_weeklysums[Channel],
        "Chain-Independent", ACT_str_weeklysums[Chain-Independent],
        "NCA Name", ACT_str_weeklysums[NCA],
        "PDCN", ACT_str_weeklysums[PDCN_CD],
        "Week_Num", ACT_str_weeklysums[ISO_WK_NBR],
        "type", "actual",
	"Value", ACT_str_weeklysums[Actual]
    ),
    SELECTCOLUMNS (
        BUD_grow,
        "Region", BUD_grow[Wholesaler Region],
        "State", BUD_grow[Wholesaler State],
        "Wslr#", BUD_grow[Wholesaler Number],
        "Channel", BUD_grow[Channel],
        "Chain-Independent", BUD_grow[Chain-Independent],
        "NCA Name", BUD_grow[NCA Name],
        "PDCN", BUD_grow[PDCN],
        "Week_Num", BUD_grow[Week_Num],
        "type", "budget",
        "Value", BUD_grow[BUD]
    ),
    SELECTCOLUMNS (
        FCST_le,
        "Region", FCST_le[Wholesaler Region],
        "State", FCST_le[Wholesaler State],
        "Wslr#", FCST_le[Wholesaler Number],
        "Channel", FCST_le[Channel],
        "Chain-Independent", FCST_le[Chain-Independent],
        "NCA Name", FCST_le[NCA Name],
        "PDCN", FCST_le[PDCN],
        "Week_Num", FCST_le[Week_Num],
        "type", "LE",
        "Value", FCST_le[LE]
    ),
    SELECTCOLUMNS (
        FCST_wslr,
        "Region", Related(MAP_wslr[SLS_REGN_CD]),
        "State", Related(MAP_wslr[WSLR_ST_CD]),
        "Wslr#", FCST_wslr[WSLR_Number5],
        "Channel", "null",
        "Chain-Independent", "null",
        "NCA Name", "null",
        "PDCN", Related(MAP_pdcn[PDCN_CD]),
        "Week_Num", Related(DimDate[Week_Num]),
        "type", "wslr_fcst",
        "Value", FCST_wslr[FCST_Btl]
    )
)