Amy has attended:
Power BI Modelling, Visualisation and Publishing course
Power BI DAX 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
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.
