Weighted Average Rate of Percentage as a straight line in graph

[aasthana]

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.

[TomMartens]

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

[aasthana]

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 · Accepted solution]

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

[aasthana]

Yes. This was exactly what I was looking for,

Thanks a lor for quick resolution.

-Ashish