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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Resolver V
Resolver V

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
Resolver V
Resolver V

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
Solution Supplier
Solution Supplier

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.