Forum Discussion

Seth4040's avatar
Seth4040
Regular Visitor
1 year ago

Average based on Filter

I am trying to get the average of project completions per week and then when I am changing the filter as what weeks I am seeing that the average would be tied into this filter.  It is currently do the completion average for all the data which is this. 

Average = CALCULATE(COUNT('CP Jobs'[Project]),ALL('CP Jobs'))/CALCULATE(DISTINCTCOUNT('CP Jobs'[Year-Wk]),ALL('CP Jobs'))

 

 

 

"average"

2 Replies

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    Seth4040 
    Can you use below code and see if it is working or not.. if not then requesting you to provide some dummy data along with your desired output.

    Average Value = 
VAR FilteredValues = 
    CALCULATETABLE(
        VALUES(Table[Value]), 
        ALLSELECTED(Table)
    )

RETURN 
    AVERAGEX(FilteredValues, Table[Value])

    Regards,

    sanalytics

  • adgeku7's avatar
    adgeku7
    Frequent Visitor
    1 year ago

    I think the use of ALL() is creating your problem - ALL() ingnores any filters applied at the page or visualization level (example, when you select a week filter, it gets ignored).

     

    Try:

    Average = CALCULATE(COUNT('CP Jobs'[Project]))/CALCULATE(DISTINCTCOUNT('CP Jobs'[Year-Wk]))