AverageX

[Cyriackpazhe]

The following meaure  will return the count of sales with sales greater than the overall average sales. 
Calculate (Countrows(Sales), Filter (Sales, [Total]> AverageX(Sales, [Total]) ) ) 

how should i write the measure if I want the count of sales with total sales greater than the average sales of that particular category.

[quantumudit · Accepted solution]

Hello @Cyriackpazhe 
It would have been helpful if you had provided more clarifivation for your question, along with a sample dataset, a screenshot, or a dataset of the desired result you want.

However, as per my understanding of your query, I created a sample "Sales" dataset with categories in it, and I have used a single formula to calculate the count of sales that are above average.

When the categories are in context, it will calculate the sales that are above the category average, and when there is no context, it will calculate the number of sales that are above the overall sales average.

Here are the 3 DAX measures I have created:

The following DAX measure will just calculate the total sales:

Total Sales = SUM(Sales[Total])

The following DAX measure will simply calculate the average sales:

Average Sales = AVERAGE(Sales[Total])

The following DAX will calculate the count of sales that are above average. When a category is in context, it calculate the number of sales that are above the category average; otherwise, it will calculate the sales that are above the overall average.

Sales Above Avg = 
VAR _category = SELECTEDVALUE(Sales[Category])
VAR _categoryAvg = CALCULATE([Average Sales], Sales[Category] = _category)
VAR _overallAvg = CALCULATE([Average Sales], ALL(Sales))
VAR _countAbvCatergoryAvg = 
    COUNTROWS(
        FILTER(
            Sales,
            Sales[Category] = _category &&
            Sales[Total] >= _categoryAvg
        )
    )
VAR _countAbvOverallAvg = 
COUNTROWS(
    FILTER(
        Sales,
        Sales[Total] >= _overallAvg
    )
)
RETURN
IF(ISINSCOPE(Sales[Category]), _countAbvCatergoryAvg, _countAbvOverallAvg)

Here is the screenshot of the results for your reference:

Here is the screenshot of the main DAX measure:

 I am also attaching the Power BI file for your reference. 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree