Average not calculating on line graph

Resolved · Urgent Priority · Version 365

Wed 1 Feb 2023, 13:39 Delegate Amy said...

Amy has attended:
Power BI Modelling, Visualisation and Publishing course

Average not calculating on line graph

Hello,

I have been trying to use an average on a line graph, excluding the year 2020, but have been unsucessful. The 'average' amount on the graph is just pulling the 'record count' value.

I've checked both the variable entries and they are calculating the correct figures.

Please can you advise why this would be happening? It looks like the divide function isn't working. Ideally, I would like the divided value across the graph for each month:

Average total activity =
var SumofTotalActivity = calculate(sum('IP&OP SUS'[RecordCount]), NOT(SEARCH("2020", 'Dim_SUS Forecast Dates'[Year], 1, 0)))

var NumberofDataPoints = calculate(DISTINCTCOUNT('IP&OP SUS'[DateConcatenated - Date]), NOT(SEARCH("2020", 'Dim_SUS Forecast Dates'[Year], 1, 0)))

Return

calculate(Divide(SumofTotalActivity,NumberofDataPoints))

Thanks so much!

Kind Regards,

Amy

Thu 2 Feb 2023, 10:27 Trainer Jens said...

RE: Average not calculating on line graph

Hi Amy,

Thank you for the forum question.

It is always challenging to answer DAX question because a lot is depending on the data model, row context, filter context, and table context.

I have tried different logic and the below may be the solution for you.

Average total activity =
var SumofTotalActivity = calculate(sum('IP&OP SUS'[RecordCount]), Filter('Dim_SUS Forecast Dates',[Year]<>2020))

var NumberofDataPoints = calculate(DISTINCTCOUNT('IP&OP SUS'[DateConcatenated - Date]), Filter('Dim_SUS Forecast Dates',[Year]<>2020))

Return

calculate(Divide(SumofTotalActivity,NumberofDataPoints))

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 12 Jan 2024, 00:39 Trainer Rich said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Navigation Shortcuts

Ctrl + Left Arrow/Right Arrow/Up Arrow/Down Arrow: Move selected object in the respective direction.
Ctrl + Shift + Left Arrow/Right Arrow/Up Arrow/Down Arrow: Resize selected object in the respective direction.
Ctrl + Shift + Up Arrow/Down Arrow: Bring forward or send backward the selected object in the report layer.

View all Power BI hints and tips