Forum Discussion
Calculate MIN, MAX, AVERAGE with Count Rows
Dear Power BI community Can you help me with the following. I do not calculate amount but number of cases. I solve that with the DAX formula count rows. But I would like to be able to calculate th...
Dear Darek
Then I will try to explain myself better.
The database my Power BI report is based on is safety cases. One line in my main table is = one case. I have used count rows to report the number of cases by category and period: year, quarter, month, this works fine.
I want help reporting on average number of cases by category and period, e.g. the average number of cases for Category X for the last 12 months.Does it make more sense?
Yes, that makes more sense.
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.
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
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