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...
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
"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
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.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