topic Calculating difference from filtered mean/average in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1628949#M32972
<P>I am stuck trying to create either a calculated column or a measure that will give me an array of values calculated from two columns in a table. The first column contains the groups in which the values fall (Group), and the second contains the values (Value). What I want to do is to calculate the mode of the values per group and then, per row, subtract the value from that mode.</P><P><BR />Here is an example of how the calculations would look (the column Mode/Group isn't necessary and can be a variable)<BR /><STRONG>'Data table'</STRONG></P><TABLE border="1"><TBODY><TR><TD>Group</TD><TD>Value</TD><TD><EM>Mode/group</EM></TD><TD><EM>Calculated Value</EM></TD></TR><TR><TD>1</TD><TD>7</TD><TD>4</TD><TD>3</TD></TR><TR><TD>2</TD><TD>2</TD><TD>2</TD><TD>0</TD></TR><TR><TD>3</TD><TD>6</TD><TD>6</TD><TD>0</TD></TR><TR><TD>1</TD><TD>4</TD><TD>4</TD><TD>0</TD></TR><TR><TD>2</TD><TD>4</TD><TD>2</TD><TD>2</TD></TR><TR><TD>1</TD><TD>4</TD><TD>4</TD><TD>0</TD></TR><TR><TD>3</TD><TD>6</TD><TD>6</TD><TD>0</TD></TR><TR><TD>2</TD><TD>2</TD><TD>2</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>I want to be able to later show the maximum, minimum, and average value of this difference per group, and also to be able to filter it for other columns in this table. Therefore I'm not sure if a calculated column or a measure would be better. <BR /><BR />Thanks for any help.<BR /><BR /></P>Thu, 28 Jan 2021 11:57:22 GMTAlanRGroskreutz2021-01-28T11:57:22ZCalculating difference from filtered mean/average
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1628949#M32972
<P>I am stuck trying to create either a calculated column or a measure that will give me an array of values calculated from two columns in a table. The first column contains the groups in which the values fall (Group), and the second contains the values (Value). What I want to do is to calculate the mode of the values per group and then, per row, subtract the value from that mode.</P><P><BR />Here is an example of how the calculations would look (the column Mode/Group isn't necessary and can be a variable)<BR /><STRONG>'Data table'</STRONG></P><TABLE border="1"><TBODY><TR><TD>Group</TD><TD>Value</TD><TD><EM>Mode/group</EM></TD><TD><EM>Calculated Value</EM></TD></TR><TR><TD>1</TD><TD>7</TD><TD>4</TD><TD>3</TD></TR><TR><TD>2</TD><TD>2</TD><TD>2</TD><TD>0</TD></TR><TR><TD>3</TD><TD>6</TD><TD>6</TD><TD>0</TD></TR><TR><TD>1</TD><TD>4</TD><TD>4</TD><TD>0</TD></TR><TR><TD>2</TD><TD>4</TD><TD>2</TD><TD>2</TD></TR><TR><TD>1</TD><TD>4</TD><TD>4</TD><TD>0</TD></TR><TR><TD>3</TD><TD>6</TD><TD>6</TD><TD>0</TD></TR><TR><TD>2</TD><TD>2</TD><TD>2</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>I want to be able to later show the maximum, minimum, and average value of this difference per group, and also to be able to filter it for other columns in this table. Therefore I'm not sure if a calculated column or a measure would be better. <BR /><BR />Thanks for any help.<BR /><BR /></P>Thu, 28 Jan 2021 11:57:22 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1628949#M32972AlanRGroskreutz2021-01-28T11:57:22ZRe: Calculating difference from filtered mean/average
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1628997#M32976
<P>Hi <LI-USER uid="124493"></LI-USER> </P>
<P>Try this for your calculated column</P>
<P> </P>
<LI-CODE lang="markup">New column =
VAR mode_ =
MINX (
TOPN (
1,
CALCULATETABLE (
ADDCOLUMNS (
DISTINCT ( Table1[Value] ),
"Frequency", CALCULATE ( COUNT ( Table1[Value] ) )
),
ALLEXCEPT ( Table1, Table1[Group] )
),
[Frequency], 0
),
Table1[Value]
)
RETURN
Table1[Value] - mode_
</LI-CODE>
<P> </P>
<P> </P>
<P> </P>
<P>and check this out for the MODE pattern</P>
<P><A href="https://www.daxpatterns.com/statistical-patterns/#" target="_blank" rel="noopener">https://www.daxpatterns.com/statistical-patterns/#</A></P>
<P> </P>
<TABLE style="height: 100px; width: 100%; border-collapse: collapse; border-style: groove; border-color: #0c1c49; background-color: #00675f;" border="0" width="100%">
<TBODY>
<TR style="height: 15px;">
<TD width="15.961305925030231%" style="width: 92px; height: 15px;">
<P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="SU18_powerbi_badge" style="width: 82px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/258830iAF7B406DD96B3746/image-dimensions/82x82?v=v2" width="82" height="82" role="button" title="SU18_powerbi_badge" alt="SU18_powerbi_badge" /></span></P>
</TD>
<TD width="84.03869407496977%" style="width: 485px; height: 15px; text-align: left;"><FONT color="#FFFFFF"><SPAN style="color: #ffffff;"><STRONG>Please accept the solution when done and consider <FONT color="#FF9900">giving a thumbs up if posts are helpful.</FONT></STRONG></SPAN></FONT><FONT color="#FFFFFF"><SPAN style="color: #ffffff;"> </SPAN> </FONT><BR />
<P><STRONG><FONT size="4.5" color="#FF99CC">Contact me privately for support with any larger-scale BI needs, tutoring, etc.</FONT></STRONG></P>
<P><FONT size="3" color="#FF99CC"><FONT color="#FFFFFF"><SPAN style="color: #ffffff;">Cheers</SPAN></FONT></FONT></P>
</TD>
</TR>
</TBODY>
</TABLE>
<P> </P>Thu, 28 Jan 2021 14:08:37 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1628997#M32976AlB2021-01-28T14:08:37ZRe: Calculating difference from filtered mean/average
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1629203#M32987
<P>Thanks, that did the trick. </P>Thu, 28 Jan 2021 14:07:39 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-difference-from-filtered-mean-average/m-p/1629203#M32987AlanRGroskreutz2021-01-28T14:07:39Z