Trying to add Weighted Average Measure to Bar Chart in Power BI

[gmat]

I'm struggling to include a weighted average measure in a bar chart visualization in Power BI Desktop. This measure is vital for tracking our core performance indicators, and I need to find a way to display it effectively.

Issue:

• Objective: I want to visualize the month-over-month trend of a weighted average measure on a bar chart.

• Challenge: Although I've created the weighted average measure as a new measure, I'm unable to add it directly to the bar chart. It seems Power BI doesn't allow measures to be added to certain visuals like bar charts.

Below is a screenshot of the chart I am working on. The green trend line on the secondary Y axis is what I am trying to replicate.. I have also included the measure I am trying to add into the visualization. Please let me know if anyone has some type of work around for this. 

Spread =

VAR IRR_amt = SUM([IRR_dollars])

VAR capex_AMT = SUM(Capex_dollars])

VAR wacc_amt = SUM(WACC_dollars)

RETURN

    IF(

        NVI_AMT <> 0,

        ROUND(((IRR_dollars / capex_dollars) - (WACC_dollars / capex_dollars)) * 10000, 0) & " bps",

        BLANK()

    )

[Greg_Deckler]

@gmat Maybe this? Better Weighted Average per Category - Microsoft Fabric Community

[gmat]

My DAX expression works, it's adding to the graph that is giving me trouble. 

[Greg_Deckler]

@gmat It looks like a Line and clustered column chart. You can add multiple values to the Column y-axis and Line y-axis. 

[gmat]

Yes, I attempted to drag my weighted average measure (spread) to the line Y-axis however it does not process or add it to the visual. It doesn't do anything. I am guess this has something to do with powerbi functionality?

[Greg_Deckler]

@gmat What is your visual configuration? Are you using a legend or small multiples? Can you share an image that is a screen shot of your visual's configuration?

[gmat]

This is how I need the visual to look like... However, the current spread field that is list on the Line y axis is just an average of the spread calc for each indivdual unit.. which is not a weighted average. I am trying to include the spread measure highlighted in yellow which is the  weighted average DAX expression that i pasted above. The issue is that I am unable to drag this measure into the same spot (line y axis)

[Greg_Deckler · Accepted solution]

@gmat Yeah, I can't replicate it, see PBIX below. If you can provide some kind of sample data or PBIX that replicates the issue can perhaps see what is going on.

[gmat]

Date	Capex	Unit Count	WACC PCT	IRR PCT	WACC dollars	IRR dollars	Weighted avg Spread
3/1/2023	$        112,000	1	7.80%	8.20%	8,736.00	9,184.00	40
3/1/2023	$        112,000	1	7.80%	8.60%	8,736.00	9,632.00	80
3/1/2023	$        112,000	1	7.80%	9.20%	8,736.00	10,304.00	140
3/1/2023	$        115,000	1	7.80%	9.50%	8,970.00	10,925.00	170
3/1/2023	$        112,000	1	7.80%	9.50%	8,736.00	10,640.00	170
3/1/2023	$        112,000	1	7.80%	9.50%	8,736.00	10,640.00	170
3/1/2023	$        100,000	1	7.80%	9.50%	7,800.00	9,500.00	170
3/1/2023	$        116,000	1	7.80%	9.50%	9,048.00	11,020.00	170
3/1/2023	$        112,000	1	7.80%	9.50%	8,736.00	10,640.00	170
4/1/2023	$        120,000	1	7.80%	9.50%	9,360.00	11,400.00	170
4/1/2023	$        185,000	2	7.80%	9.50%	14,430.00	17,575.00	170
4/1/2023	$        185,000	1	7.80%	9.50%	14,430.00	17,575.00	170
4/1/2023	$        185,000	1	7.80%	9.50%	14,430.00	17,575.00	170
4/1/2023	$        185,000	1	7.80%	9.50%	14,430.00	17,575.00	170
4/1/2023	$        185,000	1	7.80%	9.80%	14,430.00	18,130.00	200
4/1/2023	$        115,000	1	7.80%	8.50%	8,970.00	9,775.00	70
4/1/2023	$        185,000	1	7.80%	8.20%	14,430.00	15,170.00	40
4/1/2023	$        100,000	1	7.80%	8.20%	7,800.00	8,200.00	40
4/1/2023	$        185,000	1	7.80%	8.20%	14,430.00	15,170.00	40
4/1/2023	$        130,000	1	7.80%	8.20%	10,140.00	10,660.00	40
4/1/2023	$        185,000	1	7.80%	8.20%	14,430.00	15,170.00	40
4/1/2023	$        185,000	1	7.80%	8.20%	14,430.00	15,170.00	40
4/1/2023	$        150,000	1	7.80%	8.20%	11,700.00	12,300.00	40
4/1/2023	$        112,000	1	7.80%	8.20%	8,736.00	9,184.00	40
4/1/2023	$        100,000	1	7.80%	8.45%	7,800.00	8,450.00	65
4/1/2023	$        185,000	1	7.80%	8.45%	14,430.00	15,632.50	65
4/1/2023	$        185,000	1	7.80%	8.45%	14,430.00	15,632.50	65
4/1/2023	$        185,000	1	7.80%	8.45%	14,430.00	15,632.50	65
5/1/2023	$        140,000	1	7.80%	8.45%	10,920.00	11,830.00	65
5/1/2023	$          60,000	1	7.80%	9.50%	4,680.00	5,700.00	170
5/1/2023	$          60,000	1	7.80%	9.50%	4,680.00	5,700.00	170
5/1/2023	$          60,000	1	7.80%	9.50%	4,680.00	5,700.00	170
5/1/2023	$          60,000	1	7.80%	9.80%	4,680.00	5,880.00	200
5/1/2023	$        130,000	1	7.80%	8.50%	10,140.00	11,050.00	70
5/1/2023	$        130,000	1	7.80%	8.20%	10,140.00	10,660.00	40
5/1/2023	$        130,000	1	7.80%	8.20%	10,140.00	10,660.00	40
5/1/2023	$        130,000	2	7.80%	8.20%	10,140.00	10,660.00	40
5/1/2023	$        130,000	1	7.80%	8.20%	10,140.00	10,660.00	40
6/1/2023	$        140,000	1	7.80%	8.20%	10,920.00	11,480.00	40
6/1/2023	$        140,000	1	7.80%	8.20%	10,920.00	11,480.00	40
6/1/2023	$        140,000	1	7.80%	8.20%	10,920.00	11,480.00	40
6/1/2023	$        140,000	1	7.80%	8.20%	10,920.00	11,480.00	40
6/1/2023	$        140,000	1	7.80%	8.45%	10,920.00	11,830.00	65
6/1/2023	$        160,000	1	7.80%	8.45%	12,480.00	13,520.00	65
6/1/2023	$        160,000	1	7.80%	8.45%	12,480.00	13,520.00	65
6/1/2023	$        160,000	1	7.80%	8.45%	12,480.00	13,520.00	65
6/1/2023	$        115,000	1	7.80%	8.45%	8,970.00	9,717.50	65
6/1/2023	$        160,000	1	7.80%	8.20%	12,480.00	13,120.00	40
6/1/2023	$        160,000	1	7.80%	8.20%	12,480.00	13,120.00	40