cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lor_Bla123
New Member

Average per region per month

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Average per region per month

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
)

12.PNG


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.

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.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Average per region per month

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
)

12.PNG


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.

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.

View solution in original post

Community Support
Community Support

Re: Average per region per month

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.

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors