topic Fixed & Dynamic Values with Slicer in Desktop
https://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770041#M371045
<P>Hi,</P><P> </P><P>So I am currently struggling with a problem where I am trying use a slicer to filter a column of values, whilst also always keeping a value (not included in the slicer).</P><P> </P><P>A simplified example of the data is:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD></TR><TR><TD>A</TD><TD>2018</TD><TD>11</TD></TR><TR><TD>A</TD><TD>2019</TD><TD>13</TD></TR><TR><TD>A</TD><TD>2020</TD><TD>19</TD></TR><TR><TD>B</TD><TD>2018</TD><TD>10</TD></TR><TR><TD>B</TD><TD>2019</TD><TD>12</TD></TR><TR><TD>B</TD><TD>2020</TD><TD>17</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>I want to be able to use a slicer to be left with:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD><TD>New Col</TD></TR><TR><TD>A</TD><TD>2018</TD><TD>11</TD><TD>11</TD></TR><TR><TD>A</TD><TD>2019</TD><TD>13</TD><TD>13</TD></TR><TR><TD>A</TD><TD>2020</TD><TD>19</TD><TD>19</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>When I filter to A, and </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD><TD>New Col</TD></TR><TR><TD>B</TD><TD>2018</TD><TD>10</TD><TD>10</TD></TR><TR><TD>B</TD><TD>2019</TD><TD>12</TD><TD>12</TD></TR><TR><TD>B</TD><TD>2020</TD><TD>17</TD><TD>17</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>when I filter to B. Any help or ideas would be much appreciated. To give a bit more context this is to create a waterfall to compare two categories (one being always fixed the other being dynamically selected).</P><P> </P>Mon, 19 Aug 2019 16:39:32 GMTDavidson9192019-08-19T16:39:32ZFixed & Dynamic Values with Slicer
https://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770041#M371045
<P>Hi,</P><P> </P><P>So I am currently struggling with a problem where I am trying use a slicer to filter a column of values, whilst also always keeping a value (not included in the slicer).</P><P> </P><P>A simplified example of the data is:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD></TR><TR><TD>A</TD><TD>2018</TD><TD>11</TD></TR><TR><TD>A</TD><TD>2019</TD><TD>13</TD></TR><TR><TD>A</TD><TD>2020</TD><TD>19</TD></TR><TR><TD>B</TD><TD>2018</TD><TD>10</TD></TR><TR><TD>B</TD><TD>2019</TD><TD>12</TD></TR><TR><TD>B</TD><TD>2020</TD><TD>17</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>I want to be able to use a slicer to be left with:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD><TD>New Col</TD></TR><TR><TD>A</TD><TD>2018</TD><TD>11</TD><TD>11</TD></TR><TR><TD>A</TD><TD>2019</TD><TD>13</TD><TD>13</TD></TR><TR><TD>A</TD><TD>2020</TD><TD>19</TD><TD>19</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>When I filter to A, and </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Category</TD><TD>Year</TD><TD>Value</TD><TD>New Col</TD></TR><TR><TD>B</TD><TD>2018</TD><TD>10</TD><TD>10</TD></TR><TR><TD>B</TD><TD>2019</TD><TD>12</TD><TD>12</TD></TR><TR><TD>B</TD><TD>2020</TD><TD>17</TD><TD>17</TD></TR><TR><TD>Fixed</TD><TD>2018</TD><TD>15</TD><TD>15</TD></TR><TR><TD>Fixed</TD><TD>2019</TD><TD>14</TD><TD>14</TD></TR><TR><TD>Fixed</TD><TD>2020</TD><TD>20</TD><TD>20</TD></TR></TBODY></TABLE><P> </P><P>when I filter to B. Any help or ideas would be much appreciated. To give a bit more context this is to create a waterfall to compare two categories (one being always fixed the other being dynamically selected).</P><P> </P>Mon, 19 Aug 2019 16:39:32 GMThttps://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770041#M371045Davidson9192019-08-19T16:39:32ZRe: Fixed & Dynamic Values with Slicer
https://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770400#M371233
<P>Hi <LI-USER uid="157454"></LI-USER> ,</P>
<P> </P>
<P>We can create a measure as below and put it to the visual.</P>
<PRE>Measure =
VAR a =
SELECTEDVALUE ( slic[Category] )
RETURN
IF (
ISFILTERED ( slic[Category] )
&& a = MAX ( 'Table'[Category] ),
BLANK (),
1
)
</PRE>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 446px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/186015iD1137C4C38D8563A/image-size/large?v=1.0&px=999" title="Capture.PNG" alt="Capture.PNG" /></span></P>
<P> </P>Tue, 20 Aug 2019 02:45:57 GMThttps://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770400#M371233v-frfei-msft2019-08-20T02:45:57ZRe: Fixed & Dynamic Values with Slicer
https://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770670#M371332
<P>Hi <LI-USER uid="71574"></LI-USER> , sorry but this solution filters to the two categories <STRONG>not </STRONG>selected, is there a way of doing so that we can always have Fixed shown and switch between the other two? Thanks</P>Tue, 20 Aug 2019 08:08:37 GMThttps://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770670#M371332Davidson9192019-08-20T08:08:37ZRe: Fixed & Dynamic Values with Slicer
https://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770844#M371405
<P>I managed to solve my problem but I feel like it is a non-optimal solution.</P><P> </P><P>I did this by creating a calculated table and 3 measures:</P><P> </P><DIV><DIV><SPAN>slic = DISTINCT('Table'[Category]) // This is linked to the main table</SPAN></DIV><DIV> </DIV><DIV><DIV><DIV><SPAN>Fixed Measure = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Category] == "Fixed"))</SPAN></DIV><DIV><DIV><DIV><SPAN>Swap Measure = SUM('Table'[Value])</SPAN></DIV><DIV><DIV><DIV><SPAN>Resulting Measure = CALCULATE([Fixed Measure], ALL(slic[Category])) + [Swap Measure]</SPAN></DIV><DIV> </DIV><DIV><SPAN>The resulting measure column is what I was looking for, when you are slicing by the new "slic" table. </SPAN><SPAN>If anyone has any recomendations that can implement the same functionality in an improved way please let me know.</SPAN></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV>Tue, 20 Aug 2019 10:42:47 GMThttps://community.powerbi.com/t5/Desktop/Fixed-amp-Dynamic-Values-with-Slicer/m-p/770844#M371405Davidson9192019-08-20T10:42:47Z