Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PPD
Helper I
Helper I

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

Capture.JPG

 

Excel calculation for Customer Region:

 

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

 

Capture.JPG

Average by Customer Region.JPG

 

 

 

 

 

 

 

 

Thanks in advance for your assistance.

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
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'))

38.png

 

Please check attached pbix for more details.

 

Best Regards,
Community Support Team _ Eason

View solution in original post

8 REPLIES 8
PPD
Helper I
Helper I

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

v-easonf-msft
Community Support
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'))

38.png

 

Please check attached pbix for more details.

 

Best Regards,
Community Support Team _ Eason

Hi @v-easonf-msft Eason, 

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

PPD
Helper I
Helper I

@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

Hi,

I believe your question has been answered.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PPD
Helper I
Helper I

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

Hi,

Share the download link of the Excel workbook.  Also, share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.