Hide average line in column chart when only one bar is displayed

[zdenek_moravec]

Dear colleagues

I have a column chart with a date on x-axis and count of orders on the y-axis. I would like to display an average line over the bars, which displays an average value of the displayed bars values.

An easy solution is to switch to “clustered column chart”, which allows to enable average line on the “further analysis” tab. Everything looks good:

until the x-axis shows only one period (one day, one month or one year). Then the avg line is distracting and actually useless:

So my question is, how to set a column chart with an average line, which disappears, when the chart shows only one value/bar. 

See example AVG line in stacked column chart in case of one value.pbix.

I believe in a measure, which can detect number of periods displayed and which calculates the average of the bar values or sets the line value to blank based on count of displayed periods. One constrain is, that the date x-axis is managed by field parameters.

Thank You very much for any hint.

Zdenek Moravec

Cesky Krumlov, Czechia

[MFelix · Accepted solution]

Hi @zdenek_moravec ,

For this you need to create a measure with the following syntax:

Average Line = SWITCH(
			TRUE(),
			SELECTEDVALUE(DateParameters[DateParameters Order]) = 0 &&
			COUNTROWS(FILTER(
				SUMMARIZE(
					DIM_Calendar,
					DIM_Calendar[Date],
					"C", [CountOfOrders]
				),
				[C] <> BLANK()
			)) > 1,

        AVERAGEX(
				ALLSELECTED(DIM_Calendar[Date]),
				[CountOfOrders]
			),
			SELECTEDVALUE(DateParameters[DateParameters Order]) = 1&&
			COUNTROWS(FILTER(
				SUMMARIZE(
					DIM_Calendar,
					DIM_Calendar[Month],
					"C", [CountOfOrders]
				),
				[C] <> BLANK()
			)) > 1, AVERAGEX(
				ALLSELECTED(DIM_Calendar[Month]),
				[CountOfOrders]
			),
			SELECTEDVALUE(DateParameters[DateParameters Order]) = 2 &&
			COUNTROWS(FILTER(
				SUMMARIZE(
					DIM_Calendar,
					DIM_Calendar[Year],
					"C", [CountOfOrders]
				),
				[C] <> BLANK()
			)) > 1, AVERAGEX(
				ALLSELECTED(DIM_Calendar[Year]),
				[CountOfOrders]
			)
		, BLANK())

Then add the value has a constant line and not an average.

Be aware that you may need to recreate your chart from scratch since the creation of other line usually mess up the visual.

See file attach

[zdenek_moravec]

Hello @MFelix , it works like a charm! I have to debug the DAX many times yet to fully understand, anyway Your solution is nice and easy. Thank You very much!

ChatGPT review for reference:

Key Components

1. SWITCH(TRUE(), condition1, result1, condition2, result2, ...)

   • This structure evaluates multiple conditions and returns the corresponding result.

2. SELECTEDVALUE(DateParameters[DateParameters Order])

   • Determines the current selection in the DateParameters table, which decides the granularity:
     • 0 → Daily
     • 1 → Monthly
     • 2 → Yearly

3. Summarization with SUMMARIZE()

   • The measure ensures that there is more than one valid row before calculating an average.
   • The inner SUMMARIZE() groups data at the chosen level (Date, Month, or Year), retrieving the [CountOfOrders] measure.
   • The FILTER(..., [C] <> BLANK()) ensures that only non-blank values are considered.

4. Averaging with AVERAGEX()

   • If conditions are met, AVERAGEX() calculates the average number of orders at the selected date level:
     • Daily: ALLSELECTED(DIM_Calendar[Date])
     • Monthly: ALLSELECTED(DIM_Calendar[Month])
     • Yearly: ALLSELECTED(DIM_Calendar[Year])
   • ALLSELECTED() ensures that only the currently selected values in the DIM_Calendar are considered.

5. Default Case (BLANK())

   • If none of the conditions are met, the measure returns BLANK()