Months in which sessions exceed average sessions for year

[Ashish Mathur]

Hi,

I have financial years in the column area of my Pivot Table. The following calculated Field formula return the average number of sessions conducted in each financial year

=AVERAGEX(VALUES(Calendar1[MonthKey]),[Sessions conducted])

The result of this formula is correct - so no problem so far.

I now wish to compute the "Number of months (in each financial year) in which the sessions conducted exceeded the average number of session". I tried the following calculated Field formula but nothing is returned in the Pivot Table

=COUNTROWS(FILTER(SUMMARIZE(Feedback,Calendar1[FiscalYearKey],Calendar1[MonthKey],"Sessions_per_month",[Sessions conducted]),[sessions_per_month]>[Average sessions conducted]))

There is no error in the formula (as confirmed by the Check formula message) but no data is returned in the Pivot Table.

Please help.

 

[marco.russo]

I think DAX format is important to read the formula, at least to myself
This is your formula:

=
COUNTROWS ( 
    FILTER ( 
        SUMMARIZE ( 
            Feedback,
            Calendar1[FiscalYearKey],
            Calendar1[MonthKey],
            "Sessions_per_month",
            [Sessions conducted]
        ),
        [sessions_per_month] > [Average sessions conducted]
    )
)

In this case, the [Average sessions conducted] is evaluated in a context defined by the row returned by SUMMARIZE, and it corresponds to the average of just one month (so the value of the month itself).
If you want to calculate the average session conducted in a month of the year you are considering, you have to change the filter context for such a calculation in this way:

=
COUNTROWS ( 
    FILTER ( 
        SUMMARIZE ( 
            Feedback,
            Calendar1[FiscalYearKey],
            Calendar1[MonthKey],
            "Sessions_per_month",
            [Sessions conducted]
        ),
        [sessions_per_month] 
        > CALCULATE ( 
              [Average sessions conducted],
              ALL( Calendar1[MonthKey] )
          )
    )
)

Let me know if it works for your scenario.
Marco

 

[Ashish Mathur]

Hi,

That worked very well. Thank you for your help. In future, will keep in mind to indent the formula for ease of reading.