Forum Discussion

jjr333's avatar
jjr333
Advocate I
7 years ago
Solved

Count instances where Aggregated Sum is zero

I have a dimDate table and a related fact table. 

The fact table contains gas readings per day per hour.

How do I calculate the number of days per year where the average day measure ( [PM10 Ave] ) exceeds the value of 50, using DAX?

[PM10 Threshold count] returns the number of hourly readings which exceed 50.

Cheers,

J.

 

PM10 Threshold count =  // Year norm: max. 35 days/year where PM10 avg/day >= 50 microgram/m3
                        CALCULATE(COUNTA(factSanSeb[PM10]); FILTER(factSanSeb; [PM10 Ave]>=50))

 

Need Help
  • jjr333's avatar
    jjr333
    7 years ago

    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?

     

     

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.

    • jjr333's avatar
      jjr333
      Advocate I
      7 years ago

      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?

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        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