Forum Discussion
Calculate average, std for each category
Hi kralco626,
You can try to use below measure to get grouped average value:
Average = VAR idlist = CALCULATETABLE ( VALUES ( 'Work'[Work ID] ), FILTER ( ALLSELECTED('Work'), 'Work'[Work Type] = MAX ( 'Work'[Work Type] ) ) ) RETURN DIVIDE ( SUMX ( FILTER (ALLSELECTED( Entries), [Work ID] IN idlist ), [Hours] ), COUNTROWS ( idlist ), 0 )Regards,
Xiaoxin Sheng
OK, so you put the Sum of hours in the hours table instead of the Work table.
That does seem to help.
However, why is the standard devision still calculated from the Hours.Hours field? Wouldn't this still give the standard devsion of the entires, not of the work items.
For example, If I filter out so I only view Work ID 5, I get a standard devision of .82. With only one work item, I should get a standard devision of 0.
OK, so I think I got it to work. I created a calculated column on the Work table that just points to the Measure on the Entries table.
On Entries you added: Sum of Hours = SUM(Hours[Hours])
On the Work table I added: Hours = Hours[Sum of Hours]
Now I can use the Work.Hours field in my visualization and choose if I want sum, count, std, etc.
That seems to have worked nicely
Also, just FYI for anyone else tyring to do this, I changed the filkter type on the relationship to both, so that if the entries table is filtered, the calculations are updated as well.
So I have this working in my dataset. The only hurdle is that I'm required to have the filter set to Both, and in my real data model with many tables, I can't do this for all the applicable relationships.
So this gets me one step closer, but not all the way there.
glad to hear you are making progress, need to know what the relationship is with another table and how it has impact that you are not getting expected result.
can you share picture of relationship model and the expected result?
