topic Re: Calculate total with filters in place in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1906907#M41177
<P>This measure gives you the expected output of 23.</P><P>I'm not clear that it'll work on your actual data though. Your screenshot has an additional field, discountjoin, that isn't explained where it fits in the model.</P><P> </P><DIV><DIV><SPAN>Check Total = </SPAN></DIV><DIV><SPAN>VAR _InscopeChecks = VALUES('Check Items'[Check number])</SPAN></DIV><DIV><SPAN>VAR _Result = </SPAN></DIV><DIV><SPAN>CALCULATE(</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>SUM('Check Items'[Amount]),</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>REMOVEFILTERS(),</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>_InscopeChecks</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>_Result</SPAN></DIV></DIV>Thu, 17 Jun 2021 15:12:20 GMTPaulOlding2021-06-17T15:12:20ZCalculate total with filters in place
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904792#M41118
<P>This should be super simple but I can't get it to work. I need to calculate the total check amount where if I filter for Ice Cream, I should get all of the item total for the checks that Ice Cream appeared on.</P><P> </P><P>Here is an example of my data:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="duncanh_0-1623879282640.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/535095i6FD6ED9E87CE3552/image-size/medium?v=v2&px=400" role="button" title="duncanh_0-1623879282640.png" alt="duncanh_0-1623879282640.png" /></span></P><P>So if I have ice cream selected in a slicer, I need the total of checks 1 and 2, and not check 3.</P><P>Ideal output: <STRONG>23 (totals of checks 1 and 2)</STRONG></P><P>More details:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="duncanh_0-1623886176898.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/535133iF389D1382035ED46/image-size/medium?v=v2&px=400" role="button" title="duncanh_0-1623886176898.png" alt="duncanh_0-1623886176898.png" /></span></P><P>I pretty much need that second column to equal the third column when the second column is populated. The third column is calculate(sum(amount), all(item)) . The second column is just a sum of the amount. </P><P> </P><P>As always all help is appreciated!</P><P> </P>Thu, 17 Jun 2021 00:01:07 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904792#M41118duncanh2021-06-17T00:01:07ZRe: Calculate total with filters in place
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904813#M41121
<P>This is what the function <A href="https://dax.guide/allselected/" target="_self">ALLSELECTED</A> is generally used for.</P><P> </P><P>Your total with slicer filtering might look like this (or not depending on exactly what filters you want to include/exclude):</P><LI-CODE lang="markup">CALCULATE ( SUM ( Data[Amount] ), ALLSELECTED () )</LI-CODE><P> </P>Wed, 16 Jun 2021 22:20:57 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904813#M41121AlexisOlson2021-06-16T22:20:57ZRe: Calculate total with filters in place
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904925#M41124
<P>Thanks for the input. I don't think this is what I need. I added allselected(item) to the sum and it returns the exact same thing as just a regular sum. I added some more details in the description. Maybe that clears things up</P>Thu, 17 Jun 2021 00:17:51 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1904925#M41124duncanh2021-06-17T00:17:51ZRe: Calculate total with filters in place
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1906779#M41176
<P>You didn't try quite what I suggested then. If you write ALLSELECTED ( Data[Item] ), then only the filter context on the [Item] column is reset to the slicer selection but the local filter context from e.g. [discountjoin] is still in place. Since I don't know what tables you have in your model, I suggested the broadest use of ALLSELECTED (without any table or column arguments). If this is too broad, you can modify it accordingly but I suspect applying on that single column may be too narrow.</P>Thu, 17 Jun 2021 14:05:10 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1906779#M41176AlexisOlson2021-06-17T14:05:10ZRe: Calculate total with filters in place
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1906907#M41177
<P>This measure gives you the expected output of 23.</P><P>I'm not clear that it'll work on your actual data though. Your screenshot has an additional field, discountjoin, that isn't explained where it fits in the model.</P><P> </P><DIV><DIV><SPAN>Check Total = </SPAN></DIV><DIV><SPAN>VAR _InscopeChecks = VALUES('Check Items'[Check number])</SPAN></DIV><DIV><SPAN>VAR _Result = </SPAN></DIV><DIV><SPAN>CALCULATE(</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>SUM('Check Items'[Amount]),</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>REMOVEFILTERS(),</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>_InscopeChecks</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV class="lia-indent-padding-left-30px"><SPAN>_Result</SPAN></DIV></DIV>Thu, 17 Jun 2021 15:12:20 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-total-with-filters-in-place/m-p/1906907#M41177PaulOlding2021-06-17T15:12:20Z