Weighted Average Not Charting

[unsuperuser13]

I am trying to create a line graph with trends by individual markets, and a total weighted average of all markets selected via slicer. I have calculated a weighted average measure using this formula.

Weighted Avg = sumx('Table'[Value]*calculate(sum('Table'[Weighting])))/sum(Table'[Weighting])

The problem is it appears to be calculating a weighted average for each individual market and plotting it over the respective lines vs showing me a weighted average for all markets. (Of course the weighted average for one item = the item)

Am I doing something wrong/is there a workaround for this?

[lbendlin · Accepted solution]

The issue is actually somewhere else.  In a standard line chart you can either have a measure and an legend, or multiple measures. You cannot have both.

[unsuperuser13]

Here is the structure of my data:

Market		Months Active	Available	Sold	Success Rate
A		4	10	4	40%
A		3	8	3	38%
A		2	7	2	29%
A		1	6	1	17%
B		3	7	3	43%
B		2	5	2	40%
B		1	3	1	33%
C		2	3	3	100%
C		1	2	1.5	75%
D		1	2	1	50%

Here's my process:

Success	0	1	2	3	4
Market A	0%	17%	29%	38%	40%
Market B	0%	33%	40%	43%
Market C	0%	75%	100%
Market D	0%	50%
Weighted Avg	0.0%	34.7%	46.9%	40.3%	40.0%
Units Available
Market A	0	6	7	8	10
Market B	0	3	5	7	0
Market C	0	2	3	0	0
Market D	0	2	0	0	0
Total	0	13	15	15	10
Percent Weighting
Market A	0%	46%	47%	53%	100%
Market B	0%	23%	33%	47%	0%
Market C	0%	15%	20%	0%	0%
Market D	0%	15%	0%	0%	0%
% Weight*Success
Market A	0.0%	7.8%	13.5%	20.3%	40.0%
Market B	0.0%	7.6%	13.3%	20.1%	0.0%
Market C	0.0%	11.5%	20.0%	0.0%	0.0%
Market D	0.0%	7.7%	0.0%	0.0%	0.0%
Weighted Avg	0.0%	34.7%	46.9%	40.3%	40.0%

Here's my desired output:

I can get everything except the weighted average (on my graph). The weighted average calc I reference in my initial comment seems to calculate for each market individually, so when I graph, it is overlaying a weighted average for each market on top of the individual market lines. I want to be able to graph a total weighted average that adjusts based on slicers and filters.

[lbendlin · Accepted solution]

The issue is actually somewhere else.  In a standard line chart you can either have a measure and an legend, or multiple measures. You cannot have both.

[unsuperuser13]

Does this mean PowerBI won't allow me to get both on same graph?

[lbendlin · Accepted solution]

You would need to use a custom visual, for example Deneb.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...