Forum Discussion

somnath6309's avatar
somnath6309
Helper I
2 years ago

Top categories and subcategories using Calculate Function

we want to produce a dashboard that shows the category and subcategory of products that sold more than twice the average sales amount. 
The folloiwng code produces the table : 

BestCategories =
VAR Subcategories =
     ALL ( 'Product'[Category], 'Product'[Subcategory] )
     VAR AverageSales =
      AVERAGEX (
              Subcategories,
             SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
               )
VAR TopCategories =
           FILTER (
                 Subcategories,
                     VAR SalesOfCategory =
                     SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
RETURN
SalesOfCategory >= AverageSales * 2
)
RETURN
TopCategories
However, is it possible to write a shorter and more efficient code using CALCULATE and Filter context ?

I have attached the Model View of the data set and the Out Put of the Code for reference.

Regards,
Somnath6309

 



 

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi somnath6309 ,

     

    You can use the following DAX code:

    BestCategories =
CALCULATETABLE (
    SUMMARIZE (
        Sales,
        'Product'[Category],
        'Product'[Subcategory],
        "Total Sales", SUM ( Sales[Quantity] * Sales[Net Price] )
    ),
    FILTER (
        ALLSELECTED ( 'Product'[Category], 'Product'[Subcategory] ),
        [Total Sales] >= 2 * AVERAGE ( Sales[Quantity] * Sales[Net Price] )
    )
)

    Hope it helps.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.