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

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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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