DAX - Calculated measure- help required

[nitinbalodi]

Hello all,

I am trying to create a measure where I should get the average of tickets created / total headcount for each city (I am able to do that).

Issue comes up when all cities are selected from slicer and it shows me the total of all cities, instead of overall average.  I have shared a Power BI file with sample data and required output is mentioned at the bottom of file.

Sample Power BI file is available at below location: 

Sample Power BI file 

Do let me know, if more clairty is required.

[kennykhor]

The model wasn't accurately defined. Data and Headcount shall be linked by both City and Month

Necessary update of formulas as below:

1. Ticket per HC = COUNT(Data[Ref No.]) / SUM(Headcount[Headcount])
2. KeyCityMonth = [City] & "-" & FORMAT([Month],"YYYYMM")

[jaipal]

@nitinbalodi  hi create a new measure and add that measure to that measure to the cards where you wanted to display

Average 5 cities = Data[Ticket per HC] /5      (reason to use 5 is you have 5 cities in your data)

If you wan to make it Dynamic, create Measures as follows

Cities count = DISTINCTCOUNT(Data[City])

Average for all cities = Data[Ticket per HC] / [Cities count]

and then add "Average for all cities"  to the cards where you wanted to display