Discussion Complex calculations on a pivot table in Excel
https://techcommunity.microsoft.com/t5/excel/complex-calculations-on-a-pivot-table/m-p/2853021#M118211
<P>I have a large database and pivot table, with monthly data. I created a sample database to show you the problem I am having.</P><P>The underlying data looks something like this (just clipped a few lines)</P><P> </P><TABLE width="290"><TBODY><TR><TD width="64">account</TD><TD width="98">name</TD><TD width="64">month</TD><TD width="64">sales</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Jan-21</TD><TD>13</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Feb-21</TD><TD>7</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Mar-21</TD><TD>8</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Apr-21</TD><TD>2</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Jan-21</TD><TD>4</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Feb-21</TD><TD>7</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Mar-21</TD><TD>9</TD></TR></TBODY></TABLE><P> </P><P>The resulting pivot returns this</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="LorneS_0-1634414849911.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/317886i4CF910A8F9703DD9/image-size/medium?v=v2&px=400" role="button" title="LorneS_0-1634414849911.png" alt="LorneS_0-1634414849911.png" /></span></P><P>What I want to do is display only the accounts where sales in 2 of the last 3 months are 5 or less.</P><P>I tried calculated items - but on my original pivot table this results in an error " too many records" </P><P>I've tried other iterations, none work without a ton of manual intervention.</P><P>Can someone point me in the direction of a solution or a hack?</P><P> </P>Sat, 16 Oct 2021 20:10:17 GMTLorneS2021-10-16T20:10:17ZComplex calculations on a pivot table
https://techcommunity.microsoft.com/t5/excel/complex-calculations-on-a-pivot-table/m-p/2853021#M118211
<P>I have a large database and pivot table, with monthly data. I created a sample database to show you the problem I am having.</P><P>The underlying data looks something like this (just clipped a few lines)</P><P> </P><TABLE width="290"><TBODY><TR><TD width="64">account</TD><TD width="98">name</TD><TD width="64">month</TD><TD width="64">sales</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Jan-21</TD><TD>13</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Feb-21</TD><TD>7</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Mar-21</TD><TD>8</TD></TR><TR><TD>1090</TD><TD>Hertz</TD><TD>Apr-21</TD><TD>2</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Jan-21</TD><TD>4</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Feb-21</TD><TD>7</TD></TR><TR><TD>1121</TD><TD>Avis</TD><TD>Mar-21</TD><TD>9</TD></TR></TBODY></TABLE><P> </P><P>The resulting pivot returns this</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="LorneS_0-1634414849911.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/317886i4CF910A8F9703DD9/image-size/medium?v=v2&px=400" role="button" title="LorneS_0-1634414849911.png" alt="LorneS_0-1634414849911.png" /></span></P><P>What I want to do is display only the accounts where sales in 2 of the last 3 months are 5 or less.</P><P>I tried calculated items - but on my original pivot table this results in an error " too many records" </P><P>I've tried other iterations, none work without a ton of manual intervention.</P><P>Can someone point me in the direction of a solution or a hack?</P><P> </P>Sat, 16 Oct 2021 20:10:17 GMThttps://techcommunity.microsoft.com/t5/excel/complex-calculations-on-a-pivot-table/m-p/2853021#M118211LorneS2021-10-16T20:10:17ZRe: Complex calculations on a pivot table
https://techcommunity.microsoft.com/t5/excel/complex-calculations-on-a-pivot-table/m-p/2853250#M118221
<P><LI-USER uid="1187519"></LI-USER> <LI-USER uid="521"></LI-USER> </P><P> </P><P>Load your data set into the data model, and create a DAX measure. See tutorial below</P><P> </P><P><A href="https://youtu.be/e-CFYi52gpc" target="_blank" rel="noopener">https://youtu.be/e-CFYi52gpc</A></P><P> </P><P>If you have a DAX measure question, ask it here Mr. Sergei Baklan is the DAX expert in the community</P>Sun, 17 Oct 2021 00:29:00 GMThttps://techcommunity.microsoft.com/t5/excel/complex-calculations-on-a-pivot-table/m-p/2853250#M118221Yea_So2021-10-17T00:29:00Z