Forum Discussion

somnath6309's avatar
somnath6309
Icon for Helper I rankHelper I
3 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 = VA...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 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.