Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
modelmike
Frequent Visitor

Slicer to let users select which value is displayed on visualization

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 Smiley Wink )

1 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]
    )
)

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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

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]
    )
)

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors