Forum Discussion
Average by Days
Hi Team,
Need help in solving the below problem.
City Day Hrs
A 2-Jan-19 8
A 3-Jan-19 4
A 7-Jan-19 4
A 10-Jan-19 6
A 14-Jan-19 2
A 15-Jan-19 6
A 23-Jan-19 10
A 28-Jan-19 6
A 29-Jan-19 2
A 31-Jan-19 4
A 8-Jan-19 18
B 1-Jan-19 8
B 2-Jan-19 10
B 4-Jan-19 10
Out put
A 6.36
B 9.33
Result 7.845 In matrix table you will get that in grant total.
Other way is summarize and union
union (summarize(table,city,"Avg Value"average(value)),summarize(table,"city","Overall","Avg Value"average(value)))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
4 Replies
what is the problem . Is Average(table[hours]) not working properly ?
In matrix table you will get that in grant total.
Other way is summarize and union
union (summarize(table,city,"Avg Value"average(value)),summarize(table,"city","Overall","Avg Value"average(value)))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi Anonymous ,
In fact, you can simply use summarize function with rollup to create new table ith summarize result with its subtotal:
Table = SELECTCOLUMNS ( SUMMARIZE ( NT, ROLLUP ( NT[City] ), "AVG", AVERAGE ( NT[Hrs] ) ), "City", IF ( [City] <> BLANK (), [City], "SubTotal" ), "AVG", [AVG] )Notice: rollup function will add a new row with a blank label, selectcolumns function is used to replace the blank part as 'SubTotal'.
Regards,
Xiaoxin Sheng