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 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?
10 Replies
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.
- 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
DarekDear 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.
