Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Solved! Go to 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] ) )
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |