Forum Discussion
Calculate MIN, MAX, AVERAGE with Count Rows
However, when you calculate the average, you have to know what you're averaging over. If you say "for the last 12 months", what do you exactly mean? Let's say you pick a day, say 13 August 2019. What does it mean "to give you the average number of cases for the past 12 months"? Please clarify.
Best
D.
E.g:
- OK. This works based on the selection of years. But what if you select, say, months? Or even weeks? I can write logic that'll only show values for years, but is it what you want?
Best
Darek- In order to get MIN, MAX, MEDIAN... anything like this, just change AVERAGEX everywhere in the code to the correct function (with X on the end).
Best
Darek Maybe I've started asking for help without explaining what I wanted as the final result.
What I want is to trend monitoring my various categories. I currently have 137 different categories and want to monitor fluctuations in the number of cases by year, quarter and month.
There are just a lot of KPIs or graphs to check for unwanted fluctuations (137 categories * 4 periods = 548 KPIs or graphs).That's why I thought that if I could make a calculation:
If the number of cases in that year, quarter or month was or above the minimum or the median or maximum, it must be equal to true.Then I can make a report showing only the categories broken down by either year, quarter or month where there has been an increase in terms of the average or maximum.
It should be far less graphs or KPIs to keep an eye on against the 548.
I've done the calculation as a test in Excel and here it works:
= IF ([number of cases]> = MIN ([continuous average for the period]; [minimum for the period]; maximum for the period]; "Yes"; "No")
The period could be equal to 5 years back eg.Therefore, I would start by calculating averages as well as the minimum and maximum number of cases and that is where my first question came from.
Do I make sense?
Here's the code for averages that works correctly (I've checked it):
Average # Cases = SWITCH( TRUE(), ISINSCOPE( Dates[Date] ), -- highest granularity level first var __visibleDate = MAX( Dates[Date] ) var __datesToAverageOver = CALCULATETABLE ( VALUES( Dates[Date] ), Dates[Date] <= __visibleDate ) RETURN CALCULATE( AVERAGEX( __datesToAverageOver, [# Cases] ), ALL( Dates ) ), ISINSCOPE( Dates[year-month] ) || ( ISINSCOPE( Dates[Month] ) && HASONEVALUE( Dates[Year] ) ), var __visibleYearMonth = MAX( Dates[YearMonthNumber] ) var __monthsToAverageOver = CALCULATETABLE ( VALUES( Dates[YearMonthNumber] ), Dates[YearMonthNumber] <= __visibleYearMonth, ALL( Dates ) ) RETURN CALCULATE( AVERAGEX( __monthsToAverageOver, [# Cases] ), ALL( Dates ) ), ISINSCOPE( Dates[Year] ), -- lowest granularity level var __visibleYear = MAX( Dates[Year] ) -- Year must be stored as an int var __yearsToAverageOver = CALCULATETABLE ( VALUES( Dates[Year] ), Dates[Year] <= __visibleYear, ALL( Dates ) ) RETURN CALCULATE( AVERAGEX( __yearsToAverageOver, [# Cases] ), ALL( Dates ) ) )
Using the above you can create everything you need... For quarters you have to replicate the piece written for month/year-month. Because you can have quarters like Q1, Q2, Q3, Q4 (which are not unique across years) and 2018-Q1, 2018-Q2,... which are unique. You have to put the condition for quarters immediately after the condition for months/year-months. Once you have this measure, you can then combine it with any conditions you want and create your KPI's.
Best
Darek
Mate, since I don't know what your Dates table looks like... I've assumed that you group dates by: individual dates, months and years only. If you have any other grouping, then you have to adjust the code.
-- Assumption:
-- Dates is a proper date table and is connected
-- to T on the Date field. T is the fact table.
-- There should be a Month field in the Dates table
-- that stores just the name of the month (March) and a
-- field YearMonth that stores the unique names
-- of months in the form YYYY-Month (2019-March). There
-- must also be an int field in the Dates table, say
-- YearMonthNumber (hidden field), that establishes the
-- time sequence of YearMonth's.
[# Cases] = COUNROWS ( T )
[# Cases (Avg)] =
SWITCH( TRUE(),
ISINSCOPE( Dates[Date] ), -- highest granularity level first
var __visibleDate = MAX( Dates[Date] )
var __datesToAverageOver =
CALCULATETABLE (
VALUES( Dates[Date] ),
Dates[Date] <= __visibleDate
)
RETURN
AVERAGEX(
__datesToAverageOver,
[# Cases]
),
ISINSCOPE( Dates[YearMonth] ) ||
(
ISINSCOPE( Dates[Month] )
&&
HASONEVALUE( Dates[Year] )
),
var __visibleYearMonth = MAX( Dates[YearMonthNumber] )
var __monthsToAverageOver =
CALCULATETABLE (
VALUES( Dates[YearMonthNumber] ),
Dates[YearMonthNumber] <= __visibleYearMonth
)
RETURN
AVERAGEX(
__monthsToAverageOver,
[# Cases]
),
ISINSCOPE( Dates[Year] ), -- lowest granularity level
var __visibleYear = MAX( Dates[Year] ) -- Year must be stored as an int
var __yearsToAverageOver =
CALCULATETABLE (
VALUES( Dates[Year] ),
Dates[Year] <= __visibleYear
)
RETURN
AVERAGEX(
__yearsToAverageOver,
[# Cases]
)
)Best
Darek