Average by Days

[Anonymous]

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	

[Anonymous]

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

[amitchandak]

what is the problem . Is Average(table[hours]) not working properly ?

[Anonymous]

The problem is i want to show the average by city and then overall average.

[amitchandak · Accepted solution]

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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601