topic Re: Calculating a measure that gives me the "right" average ... in Desktop
https://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/661459#M317779
<P>Hi <LI-USER uid="12837"></LI-USER> </P><P> </P><P>I've tested this solution and it still gives me 4,6 and not 4,5... this is proving to be a hard one. Any other idea's?</P><P> </P><P>To understand it better I've looked at a couple of lessons on The ALL, ALLSELECTED and ALLEXCEPT filters today and am learning more and more. Thanks for that. </P><P> </P><P>One additional question / complication: In my real table respondents are able to score 0 to indicate that they did not want to answer that question. I am trying to figure out where to filter out those values. i.e. where do I insert a filter that ensures only values are considered that fullfil the condition Table[score]<>0? For the interim I have dealt with this by changing all 0 scores to null upon import of the data.</P><P> </P><P>Kind regards,</P><P> </P><P>Max</P>Thu, 04 Apr 2019 15:15:27 GMTMax_Kloosterman2019-04-04T15:15:27ZCalculating a measure that gives me the "right" average ...
https://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/660046#M316989
<P>Hi,</P><P> </P><P>Earlier I received help form <LI-USER uid="82071"></LI-USER> to resolve my question on how to structure my data. That worked However ... a new question came up that I cannot seem to resolve. It is about calculating a measure that does NOT do a weighted average <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P><P> </P><P>I want to use this measure in a visualisation so that it shows: (in table form)</P><TABLE><TBODY><TR><TD>respondent</TD><TD>questioncategory</TD><TD>question</TD><TD>score</TD><TD>averagebyquestion</TD><TD>averagebyquestioncategory</TD><TD>aveargebyrespondent</TD></TR><TR><TD>resp1</TD><TD>cat1</TD><TD>cat1.1</TD><TD>5</TD><TD>5</TD><TD>5</TD><TD>4,5</TD></TR><TR><TD>resp1</TD><TD>cat1</TD><TD>cat1.2</TD><TD>5</TD><TD>5</TD><TD>5</TD><TD>4,5</TD></TR><TR><TD>resp1</TD><TD>cat1</TD><TD>cat1.3</TD><TD>5</TD><TD>5</TD><TD>5</TD><TD>4,5</TD></TR><TR><TD>resp1</TD><TD>cat2</TD><TD>cat2.1</TD><TD>5</TD><TD>5</TD><TD>4</TD><TD>4,5</TD></TR><TR><TD>resp1</TD><TD>cat2</TD><TD>cat2.2</TD><TD>3</TD><TD>3</TD><TD>4</TD><TD>4,5</TD></TR></TBODY></TABLE><P> </P><P>Regardless of the number of questions in a category, each respondent will need to get an average based on the average over all categories. To calculate the total, each category should have one vote. In the example above. The average should be 4,5 as in the table above. I however keep getting 4,6 (the weighted average)</P><P> </P><P>My key problem seems to be how to create a function that iterates not over each row but over a new/filtered? table that contains only the average for a category. Is that at all possible, to use DAX to create a virtual table that contains a subset of rows and columns from the original table as well as the values from a measure?</P><P> </P><P>I even find it difficult to do this in excel so thinking through this problem proves to be harder than I thought it would be - even though the concept seems so straightforward.</P><P> </P><P>Kind regards,</P><P> </P><P>Max</P><P> </P><P>Earlier (resolved) post <A href="https://community.powerbi.com/t5/Desktop/Average-of-answer-by-question-with-multiple-value-types/m-p/572738?author_id=94921&collapse_discussion=true&filter=location,authorId&location=forum-board:power-bi-designer&q=average%20of&search_type=thread " target="_blank" rel="noopener">https://community.powerbi.com/t5/Desktop/Average-of-answer-by-question-with-multiple-value-types/m-p/572738?author_id=94921&collapse_discussion=true&filter=location,authorId&location=forum-board:power-bi-designer&q=average%20of&search_type=thread </A></P>Tue, 02 Apr 2019 18:19:34 GMThttps://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/660046#M316989Max_Kloosterman2019-04-02T18:19:34ZRe: Calculating a measure that gives me the "right" average ...
https://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/660585#M317293
<P><FONT face="tahoma,arial,helvetica,sans-serif">Hi <LI-USER uid="94921"></LI-USER> ,</FONT></P>
<P><FONT face="tahoma,arial,helvetica,sans-serif">You can add use allexcept to choose which fields filtered formula calculation.</FONT></P>
<P><FONT face="tahoma,arial,helvetica,sans-serif">Sample:</FONT></P>
<P> </P>
<PRE><FONT face="tahoma,arial,helvetica,sans-serif"><STRONG>Count Measure</STRONG> =
CALCULATE (
AVERAGE ( Table[Score] ),
ALLSELECTED ( Table ),
VALUES ( Table[ColumnName] )
)
<STRONG>Count Measure2</STRONG> =
CALCULATE ( AVERAGE ( Table[Score] ), ALLEXCEPT ( Table, Table[ColumnName] ) )</FONT></PRE>
<P> </P>
<P><FONT face="tahoma,arial,helvetica,sans-serif">Regards,</FONT></P>
<P><FONT face="tahoma,arial,helvetica,sans-serif">Xiaoxin Sheng</FONT></P>Wed, 03 Apr 2019 05:30:51 GMThttps://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/660585#M317293v-shex-msft2019-04-03T05:30:51ZRe: Calculating a measure that gives me the "right" average ...
https://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/661459#M317779
<P>Hi <LI-USER uid="12837"></LI-USER> </P><P> </P><P>I've tested this solution and it still gives me 4,6 and not 4,5... this is proving to be a hard one. Any other idea's?</P><P> </P><P>To understand it better I've looked at a couple of lessons on The ALL, ALLSELECTED and ALLEXCEPT filters today and am learning more and more. Thanks for that. </P><P> </P><P>One additional question / complication: In my real table respondents are able to score 0 to indicate that they did not want to answer that question. I am trying to figure out where to filter out those values. i.e. where do I insert a filter that ensures only values are considered that fullfil the condition Table[score]<>0? For the interim I have dealt with this by changing all 0 scores to null upon import of the data.</P><P> </P><P>Kind regards,</P><P> </P><P>Max</P>Thu, 04 Apr 2019 15:15:27 GMThttps://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/661459#M317779Max_Kloosterman2019-04-04T15:15:27ZRe: Calculating a measure that gives me the "right" average ...
https://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/662333#M318277
I thought I solved this question myself - until it turned out I didn't<BR /><BR />the path I tried: is to use the formula suggested (the allselected (values version ) to calculate the average for each category and then using that formula inside another calculation that divides the sum of those averages for a respondent by the distinctcount of categories for a respondent. IT came up with the correct average in the total row in the bottom of the table, however I couldn't get it to work on the rows of the table.<BR /><BR />Will remain interested in any working and preferably a very elegant approach.&nbsp;<BR /><BR />Kind regards,<BR /><BR />Max<BR /><BR />DAX I ended up trying that got me close but not quite there:<BR /><BR />Average Scores := calculate ( average( table[score], filter ( table, table[score] <>0))<BR /><BR />Average by respondent := averagex ( values ( questioncategory) , [Average Scores]))<BR /><BR />Kind regards,<BR />Max<BR />Sat, 06 Apr 2019 22:58:14 GMThttps://community.powerbi.com/t5/Desktop/Calculating-a-measure-that-gives-me-the-quot-right-quot-average/m-p/662333#M318277Max_Kloosterman2019-04-06T22:58:14Z