cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Need help with DAX measure error for Counta with criteria

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!

1 ACCEPTED SOLUTION
Super User

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])``````

7 REPLIES 7
Super User

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])``````

Helper I

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

Community Support

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.

Helper I

Sorry, there is no change to the result too.

Helper I

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".

Super User

@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.

Helper I

Sorry, there is no change in the result. 😞

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors