Calculate MIN, MAX, AVERAGE with Count Rows

[DAX_Fool]

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 the average, minimum, maximum and possibly the meridian of number of cases per week, month or year.
I've tried the standard formulas: Average, AverageX, Max, Min and Median (X), but I can't make them work with count rows.
Is there anyone who can give me a suggestion for a solution?

[Anonymous]

The meaning of this

"But I would like to be able to calculate the average, minimum, maximum and possibly the meridian of number of cases per week, month or year."

is completely unclear or even undefined. Think about what you are saying. When you calculate an average, you have some numerical quantity for each observation in your dataset. Then you sum the quantities up and divide by the number of cases. In the context of row counts this has no meaning. Since, what does it mean: the average number of cases/rows per week? If you select a certain week, what meaning does "average number of cases" have?

Please clarify what you really want to do.

Best
Darek

[DAX_Fool]

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?

[Anonymous]

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.

[DAX_Fool]

E.g:

[Anonymous]

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

[Anonymous]

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

[DAX_Fool]

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?

[Anonymous]

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

[Anonymous]

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

[Pavel_Bazlov]

Hi,

For average create a measure:

Average Countrows = Countrows (<data table>) / Countrows (<calendar dim table>)

For months you will get an average of your countrows per day for each week, for each month, for each year in your calendar dim table depending on what level of drillthrough for a calendar table you are on.

Of course you would have to have a week, month, year calculated columns in your date dim table.

If this is not what you want, could you please provide example of your data table, calendar table as well as expected output for a result.