cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aasthana Frequent Visitor
Frequent Visitor

Weighted Average Rate of Percentage as a straight line in graph

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.

 

Pic 1.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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:
image.png

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!
4 REPLIES 4
Super User
Super User

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

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

 

image.png

 

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!
aasthana Frequent Visitor
Frequent Visitor

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

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

 

Pic 3.PNG

Super User
Super User

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

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:
image.png

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!
aasthana Frequent Visitor
Frequent Visitor

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

Yes. This was exactly what I was looking for,

 

Thanks a lor for quick resolution.

 

-Ashish