topic Re: Average of a calculated measure in DAX Commands and Tips

Average of a calculated measure

kasfour — Sat, 18 Feb 2023 20:28:47 GMT

Hello everyone,

I am trying to calculate the average of all services below 100%. Each month I have those services on the dashboard as shown below.

Is there a way to calculate the average and place it on a card.

Appreciate you help.

Re: Average of a calculated measure

FreemanZ — Sun, 19 Feb 2023 00:39:13 GMT

hi kasfour

The average of what measure?

Or what measure do you have in your lower bar chart? How is it defined?

Re: Average of a calculated measure

kasfour — Sun, 19 Feb 2023 10:39:04 GMT

I measure the services uptime monthly using the below function

"Uptime = 1-DIVIDE(SUM('Uptime YTD'[DOWNTIMEin minutes]),[DayDiff(Minutes)],0)"

Re: Average of a calculated measure

kasfour — Sun, 19 Feb 2023 13:24:02 GMT

I measure the services uptime monthly using the below function

"Uptime = 1-DIVIDE(SUM('Uptime YTD'[DOWNTIMEin minutes]),[DayDiff(Minutes)],0)"

Re: Average of a calculated measure

tamerj1 — Sun, 19 Feb 2023 14:54:32 GMT

Hi kasfour

you may try

Uptime =

AVERAGEX (

VALUES ( 'Date'[YearMonth] ),

CALCULATE (

VAR Uptime = 1-DIVIDE(SUM('Uptime YTD'[DOWNTIMEin minutes]),[DayDiff(Minutes)],0)
IF (

Uptime < 1,

Uptime

)

Re: Average of a calculated measure

kasfour — Sun, 19 Feb 2023 18:52:19 GMT

Thank you tamerj1 . The below error showed when trying to add this measure

Re: Average of a calculated measure

tamerj1 — Sun, 19 Feb 2023 19:23:22 GMT

kasfour
The MonthYear column is from which table. Replace 'Table'[Column] below with the real name of that column

Uptime = AVERAGEX ( VALUES ( 'Table'[Column] ), CALCULATE ( VAR Uptime = 1 - DIVIDE ( SUM ( 'Uptime YTD'[DOWNTIMEin minutes] ), [DayDiff(Minutes)], 0 ) RETURN IF ( Uptime < 1, Uptime ) ) )

Re: Average of a calculated measure

kasfour — Mon, 20 Feb 2023 07:55:02 GMT

Thank you tamerj1 . However the result is different than what I expected. The measure is called Avg. Uptime shown below. I expected the average to be taken from the services with lower than 100% only. In that example below the avg should have been (99.85+99.72+99.66)/3 = 99.74%

I guess the solution is close now :). Thank you so much for your help so far.

Re: Average of a calculated measure

tamerj1 — Mon, 20 Feb 2023 09:34:02 GMT

kasfour
Sorry for the late response. I was stuck in a couple of meetings.

Please help me try understand the column chart visual. Previously I thought it is sliced by YearMonth which means each column (bar) is a actually a month. Having 3 columns means that there are minimum 3 months building up the filter context, however I can see that the date slicer is showing the dates of only one month (2023-Jan). Would you please clarify this point.