Forum Discussion

Cyriackpazhe's avatar
Cyriackpazhe
Helper III
1 year ago
Solved

AverageX

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 sho...
  • quantumudit's avatar
    1 year ago

    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