Need help with DAX measure error for Counta with criteria

[pooofy]

Hi,

I am trying to put together a DAX measure, but am facing some errors.

The columns of interest are "Date", "Spending per Year" and "Distinct".

The following are my current measures:

Average spending, nothing wrong with this:

Average Spending = calculate(average('Table'[Spending per Year]), filter('Table', 'Table'[Distinct]="Distinct"))

Average spending based on 3 years, nothing wrong with this:

Average spending for 3 years =
var maxyear = MAXX('Table','Table'[Date].[Year])
var minyear = maxyear - 2
var result = Calculate([Average Spending],
filter(ALL('Table'),'Table'[Date].[Year]<=maxyear && 'Table'[DAte].[Year]>=minyear))
return result

I am having trouble calculating the next one. The desired result is to count values in average spending, which are distinct (based on the distinct column) that are more than the result calculated in the measure [Average spending based on 3 years].

Count = calculate(
counta('Table'[Spending per year]),
filter(
All('Table'),(('Table'[Spending per year]>[Average spending for 3 years] && 'Table'[Distinct] = "Distinct")))))

The output of this measure is not giving me the desired result.

Thank you!

[isjoycewang · Accepted solution]

Hi @pooofy,

Is this the result you want? Count the values greater than avg. spending.

Share the demo file here so you could find more details and make it fit in your case.

Feel free to let me know if any questions 🙂

Average spending for 3 years = 
var maxyear = MAXX('Table (2)','Table (2)'[Date].[Year])
var minyear = maxyear - 2
var result = 
Calculate(AVERAGE('Table (2)'[Spending per Year]),
filter(ALL('Table (2)'[Date].[Year]),'Table (2)'[Date].[Year] <= maxyear && 'Table (2)'[Date].[Year] >= minyear))
return result

Count = // count values greater than average
VAR _average3year = [Average spending for 3 years]
RETURN
COUNTX( 
    FILTER( 'Table (2)', 
    [Spending per Year] >= _average3year), [Spending per Year])

[pooofy]

Thank you @isjoycewang !! i adapted your solution to include other criteria i had, and it works perfectly! 

[v-xinruzhu-msft]

Hi @pooofy 

You can try the following measure

Count =
CALCULATE (
    COUNTA ( 'Table'[Spending per year] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        ( 'Table'[Spending per year] > [Average spending for 3 years]
            && 'Table'[Distinct] = "Distinct" )
    )
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pooofy]

Sorry, there is no change to the result too. 

[pooofy]

Hi,

I tried the suggested measure, but there is no change to the result.

I created a column:

Column = 'Table'[Spending per Year]>[Average spending for 3 years]

but in the column, there is a value that is not giving the correct result, i.e. Spending per Year = 35, Average spending for 3 Years = 38, but the result returns "True".

[devesh_gupta]

@pooofy , Try like this:

Count = calculate(
counta('Table'[Spending per year]),
filter('Table','Table'[Spending per year]>[Average spending for 3 years] && 'Table'[Distinct] = "Distinct"))

If it helps, please mark it as an accepted solution so that other users can find it more easily.

[pooofy]

Sorry, there is no change in the result. 😞