cancel
Showing results for
Did you mean:
Frequent Visitor

## Average based on Groups

Hi, I am new to Power BI, and would like to achieve the following results for "Average by Customer" and Average by Customer Region" in the existing table.

This report is generated using Direct query mode using Tabular.

Calculation for "Average by Customer"
Measure1 = Sum by Product A
Measure2 = Sum by Product B
M% = Measure1/ Measure 2
Average by Customer = Average of M% by the count of distinct Products

Similarly , calculation for Average by Customer Region
Measure1 = Sum by Product A (Grouped by Customer Region)
Measure2 = Sum by Product B (Grouped by Customer Region)
M% = Measure1/ Measure 2
Average by Customer Region = Average of M% by the count of distinct Products

Excel calculation for Customer Region:

https://1drv.ms/x/s!ArLuftnPv6dwghbmVulAQkBdPywL?e=1cOGET

1 ACCEPTED SOLUTION
Community Support

Hi, @PPD

Try to  create measure as below:

``M% = [Measure 2] /[Measure 1]``
``Average by customer = AVERAGEX(SUMMARIZE('Table','Table'[Product],'Table'[Customer Region],"M%",[M%]),[M%])``
``Average by Customer Region = CALCULATE( [Average by customer],ALL('Table'))``

Please check attached pbix for more details.

Best Regards,
Community Support Team _ Eason

8 REPLIES 8
Frequent Visitor

I leveraged the solution provided by @v-easonf-msft  with a few modifications. It worked. Thank you!

Community Support

Hi, @PPD

Try to  create measure as below:

``M% = [Measure 2] /[Measure 1]``
``Average by customer = AVERAGEX(SUMMARIZE('Table','Table'[Product],'Table'[Customer Region],"M%",[M%]),[M%])``
``Average by Customer Region = CALCULATE( [Average by customer],ALL('Table'))``

Please check attached pbix for more details.

Best Regards,
Community Support Team _ Eason

Frequent Visitor

Hi @v-easonf-msft Eason,

Thank you for your response, and sharing the PBIX file. Please see my response below.

Frequent Visitor

@Ashish_Mathur .Attached the excel link. I couldnt attached the PBI file since this data model is created using direct query mode using Tabular.

https://1drv.ms/x/s!ArLuftnPv6dwghbmVulAQkBdPywL?e=1cOGET

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi @Ashish_Mathur . I updated the original post with the excel calculations.

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

Hi,

Share the MS Excel file with your calculations.  I want to see how have you calculated figures in the "Customer by region" column

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements