average not calculating line

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Average not calculating on line graph

Average not calculating on line graph

resolvedResolved · Urgent Priority · Version 365

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

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

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

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

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


Server loaded in 0.08 secs.