Forum Discussion
Count instances where Aggregated Sum is zero
Hi Maggie,
Thank you.
In the mean time I have used a different approach: GROUPBY() and a virtual table.
DaysExceeding50 = VAR varPM10DailyAve = GROUPBY(Sheet3;'DATETIME TABLE'[date];"varcolAvePM10";AVERAGEX(CURRENTGROUP();Sheet3[pm10])) RETURN COUNTROWS(FILTER(varPM10DailyAve;[varcolAvePM10]>=50))
Kind regards,
Julian
PS: how does one attach a pbix file?
Hi jjr333
If dimDate table have a datetime columns with format (year/month/day hh:mm:ss)
Then i create calculated columns in this table
year = [datetime].[Year] date = [datetime].[Date]
Then create measures in this table
sum = CALCULATE(SUM(Sheet3[pm10]),ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[date]))
count_hours_perday = CALCULATE(DISTINCTCOUNT('DATETIME TABLE'[datetime]),ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[date]))
average = [sum]/[count_hours_perday]
days = CALCULATE(DISTINCTCOUNT('DATETIME TABLE'[date]),FILTER(ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[year]),[average]>=50))
Please refer to my pbix to see more details.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie,
Thank you.
In the mean time I have used a different approach: GROUPBY() and a virtual table.
DaysExceeding50 = VAR varPM10DailyAve = GROUPBY(Sheet3;'DATETIME TABLE'[date];"varcolAvePM10";AVERAGEX(CURRENTGROUP();Sheet3[pm10])) RETURN COUNTROWS(FILTER(varPM10DailyAve;[varcolAvePM10]>=50))
Kind regards,
Julian
PS: how does one attach a pbix file?
Hi jjr333
When replying, do you see the "choose file" option?
If not, you could upload file to OneDrive and share the file link here.
In addition, your formula does also slove your problem, right?
Can we consider this case as closed?
Best Regards
Maggie