- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Weighted Average Rate of Percentage as a straight ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

aasthana

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
10:14 AM

Hi,

I am trying to calculate Weighted avergae of Adverse Rate which should appear as straight line. In the cluster chart, we can find Analytics Pane where We get average line but that’s giving Regular average. We need weighted average and that too in the form of straight line of Adverse Rate.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

TomMartens

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
11:42 AM

Hey,

I think you have to change the DAX of your Weighted Average to this:

Weighted Avg = --VAR __CATEGORY_VALUES = VALUES('Sheet1'[Fulfillment Center]) VAR __CATEGORY_VALUES = ALLSELECTED('Sheet1'[Fulfillment Center]) RETURN DIVIDE( SUMX( --KEEPFILTERS(__CATEGORY_VALUES), __CATEGORY_VALUES, CALCULATE([Adverse Rate] * SUM('Sheet1'[Adverse])) ), SUMX( --KEEPFILTERS(__CATEGORY_VALUES), __CATEGORY_VALUES, CALCULATE(SUM('Sheet1'[Adverse])) ) )

Then your chart looks like this:

Hopefully, this is what you are looking for.

Regards,

Tom

Hamburg - Germany

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

4 REPLIES 4

TomMartens

Super User

Re: Weighted Average Rate of Percentage as a straight line in graph

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
10:27 AM

Hey,

you have to create a measure that reflects the weighted average, then you can use the "Line and stacked column chart" or the "Line and clustered column chart" and use the measure for the Line value well

If you need help to create a measure, please consider to create a pbix or xlsx file that contains sample data and reflects your data model, upload the file to onedrive or Dropbox, and share the link.

Hopefully, this is what you are looking for.

Regards,

Tom

Hamburg - Germany

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

aasthana

Frequent Visitor

Re: Weighted Average Rate of Percentage as a straight line in graph

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
11:29 AM

I already tried to pull the avg measure in Line Values however this is not constant line. It's dynamic. If you see in the pic below.

My problem is:

1. It should consider **weighted avg** not regular average.

2. The Weighted Avg line should be **constant.**

Link of PBIX file and source file are as follows:

https://1drv.ms/f/s!Ao3I1Gsi9dPuiFYx1XozRsY_HGSg

TomMartens

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
11:42 AM

Hey,

I think you have to change the DAX of your Weighted Average to this:

Weighted Avg = --VAR __CATEGORY_VALUES = VALUES('Sheet1'[Fulfillment Center]) VAR __CATEGORY_VALUES = ALLSELECTED('Sheet1'[Fulfillment Center]) RETURN DIVIDE( SUMX( --KEEPFILTERS(__CATEGORY_VALUES), __CATEGORY_VALUES, CALCULATE([Adverse Rate] * SUM('Sheet1'[Adverse])) ), SUMX( --KEEPFILTERS(__CATEGORY_VALUES), __CATEGORY_VALUES, CALCULATE(SUM('Sheet1'[Adverse])) ) )

Then your chart looks like this:

Hopefully, this is what you are looking for.

Regards,

Tom

Hamburg - Germany

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

aasthana

Frequent Visitor

Re: Weighted Average Rate of Percentage as a straight line in graph

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2019
12:18 PM

Yes. This was exactly what I was looking for,

Thanks a lor for quick resolution.

-Ashish