Forum Discussion
Iterating through filtered values in a measure
Hi, I'm new to Power BI and I'm not really sure what should I be looking for.
But here it goes, my business scenario looks like this: I need to make visuals of averages (one average for one column, another one for another column, etc. - 5 columns) for the logged in user, based on his business units. I need to show the average for his unit (level1), for all units that have the same parent (level2), all units that have the same grandparent (level3) and for the whole organisation (level4). I created a few measures to get the current user's details, get his business unit (level1) and results for his business unit (everything is stored in dataverse, I'm taking the user's details from the SystemUser table and the business unit details from the BusinessUnit table, the table with results that I need to make averages (MainTable) - stores information about the related business unit - always level1).
I'm able to present these in a visual. I collected the business units (level 1) that have the same parent as the user's business unit - I store them in a measure, but probably in an incorrect way. Now I'm trying to go through each of those business units to filter my MainTable, to get all results for level 2. Then, I will have to do the same with level 3.
I cannot put these values in a table, since I use the UserName function - I guess I need to use measures. I would like to do something like: For each Business Unit (level 1) that shares a parent business unit as the user's, go through MainTable and calculate the average of the 5 columns. How do I do that?
Hi, Anonymous ;
I think you could use a new table which contain username column, and create a new table by the following formula:
New table = SUMMARIZE ( 'Table', [user], "useravg", AVERAGE ( 'Table'[value] ), "level1 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level1] ) ), "level2 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level2] ) ), "level3 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level3] ) ), "level4 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level4] ) ) )When username = “amy” and "peter", The final output is shown below:
or you could create a column not measure as follows:
avg level1 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level1]))avg level2 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level2]))avg level3 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level3]))avg level4 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level4]))Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
I think you could use a new table which contain username column, and create a new table by the following formula:
New table = SUMMARIZE ( 'Table', [user], "useravg", AVERAGE ( 'Table'[value] ), "level1 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level1] ) ), "level2 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level2] ) ), "level3 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level3] ) ), "level4 avg", CALCULATE ( AVERAGE ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[level4] ) ) )When username = “amy” and "peter", The final output is shown below:
or you could create a column not measure as follows:
avg level1 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level1]))avg level2 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level2]))avg level3 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level3]))avg level4 = CALCULATE(AVERAGE([value]),ALLEXCEPT('Table','Table'[level4]))Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.