Average per region per month

[Lor_Bla123]

Hi there

I'm relatively new to the world of Power BI so this may be a basic query but have been unable to find a solution. I have 2 data tables for the report I am producing - one is a raw data set (65k rows), with each row representing a task with a unique ID.

Task ID | Date created | Site | Region

1234 | 15/10/19 | SiteA | RegionA

3214 | 15/10/19 | SiteB | RegionA

3215 | 14/10/19 | SiteB | RegionA

1785 | 17/08/19 | SiteC | RegionB

8525 | 01/08/19 | SiteD | RegionB

The second table is a reference table showing 1500 sites along with their allocated region (33 regions in total).

Site | Region

SiteA | A

SiteB | A

SiteC | B

SiteD | B

SiteE | A

I want to produce a table/graph which shows average task volume per region per month - normalised for the number of sites in that region. In Excel I'd create use a pivot table to sum per region per month and then divide by the number of sites in the region but struggling to get this to work in BI. I've tried various Quick Measures and also tried creating a third summary table which just has Region name and count of site but still not getting anywhere. 

Everything I try either seems to give me the total volume per region per month or the average based on the 1500 sites and not split by region.

Any help would be much appreciated..

Thanks 

[v-lid-msft]

Hi @Lor_Bla123 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Lor_Bla123 ,

We can create measure using following formula to meet your requirement:

Average =
DIVIDE (
    COUNT ( 'TableA'[Task ID] ),
    CALCULATE (
        COUNTROWS ( 'TableB' ),
        "Region"
            & TableB[Region] IN FILTERS ( 'TableA'[Region] )
    ),
    0
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.