Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
pooofy
Helper I
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
isjoycewang
Super User
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 🙂

isjoycewang_1-1695896035467.png

 

 

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

 

View solution in original post

7 REPLIES 7
isjoycewang
Super User
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 🙂

isjoycewang_1-1695896035467.png

 

 

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

 

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

v-xinruzhu-msft
Community Support
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.

Sorry, there is no change to the result too. 

pooofy
Helper I
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".

 

devesh_gupta
Super User
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.

Sorry, there is no change in the result. 😞

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.