Forum Discussion
Default Selection in Bar Chart
Hello,
I have two bar charts (SectorReturns on left and ReturnAttribution on right in image below) that are to be filtered by sector (materials, industrials, consumer disc., etc.).
I have the filtering in place however, I want the ReturnAttribution bar chart to default to the "portfolio" sector and not show the overall attribution by sector. For example for Materials below the filtering for each sector is in place but when nothing is selected in the SectorReturns bar chart, I want the same thing as below to show for the "portfolio" sector.
Additionally, having a data label issue where I don't want the materials label to show in the y-axis for the ReturnAttribution chart. Each bar represents Excess Return, Style, Industry and Selection Effect. I want these labels to show. Table is set up like this:
Thanks!
Hi JonnyOh
My approach is through measures.
First you have to create 4 measures instead of 4 columns to get a dynamic display.
The 4 measures are basically the same, except that the referenced columns are different.
before you do this, create a table with this formula
Table 2 = VALUES('Table'[Sector])4 measure:
M Excess Return = VAR _selected = CALCULATE ( SUM ( 'Table'[Excess Retrun] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table 2'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Excess Retrun] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MIndustry = VAR _selected = CALCULATE ( SUM ( 'Table'[Industry] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Industry] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MSelection = VAR _selected = CALCULATE ( SUM ( 'Table'[Selection] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Selection] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MStyle = VAR _selected = CALCULATE ( SUM ( 'Table'[Style] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Style] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) )After this, do filter via the slicer visual.
Here is my pbix file you can reference.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi JonnyOh
My approach is through measures.
First you have to create 4 measures instead of 4 columns to get a dynamic display.
The 4 measures are basically the same, except that the referenced columns are different.
before you do this, create a table with this formula
Table 2 = VALUES('Table'[Sector])4 measure:
M Excess Return = VAR _selected = CALCULATE ( SUM ( 'Table'[Excess Retrun] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table 2'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Excess Retrun] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MIndustry = VAR _selected = CALCULATE ( SUM ( 'Table'[Industry] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Industry] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MSelection = VAR _selected = CALCULATE ( SUM ( 'Table'[Selection] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Selection] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) ) MStyle = VAR _selected = CALCULATE ( SUM ( 'Table'[Style] ), FILTER ( 'Table', [Sector] = SELECTEDVALUE ( 'Table'[Sector] ) ) ) VAR _no_selected = CALCULATE ( SUM ( 'Table'[Style] ), FILTER ( 'Table', [Sector] = "Portfolio" ) ) VAR _IFselected = MAX ( 'Table 2'[Sector] ) = SELECTEDVALUE ( 'Table'[Sector] ) RETURN IF ( _IFselected, _selected, IF ( MAX ( 'Table 2'[Sector] ) = "Portfolio", _no_selected, BLANK () ) )After this, do filter via the slicer visual.
Here is my pbix file you can reference.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.