Get the value for sub category by certain %

[Carly_717]

Hi all,

I would like to get the total sales of subcategory where the main category is more than certain %

I would like to show all the sales of sub category where the total weightage is more or equal to 30%. 
for example, the main category that is more than 30% are "Clothes 2" and "Car"
and now, I want to get all details of item in sub category which are under "Clothes 2" and "Car".

How can I solve this by creating the measure in power bi ? 

Thanks in advance.

[johnt75]

If you already have measures for [Sales] and [Weightage] you can create a new measure like

Sales for category above 30 =
VAR MainCategories =
    FILTER ( ALL ( 'Table'[Main Category] ), [Weightage] >= 0.3 )
VAR Result =
    CALCULATE ( [Sales], KEEPFILTERS ( MainCategories ) )
RETURN
    Result

[SamsonTruong · Accepted solution]

Hi @Carly_717 ,

One approach to this is by creating a filter flag measure. To create the measure, try using the following DAX:

FilterFlag =
VAR TotalSales = CALCULATE(SUM(SalesData[Sales]), ALL(SalesData))
VAR MainCategorySales =
    CALCULATE(
        SUM(SalesData[Sales]),
        ALLEXCEPT(SalesData, SalesData[MainCategory])
    )
VAR Weightage = DIVIDE(MainCategorySales, TotalSales)
RETURN
IF(Weightage >= 0.3, 1, 0)

Then add this measure to your filter pane and set it to where the FilterFlag is equal to 1.

This will filter your visual to all sales of sub category where the total weightage is more or equal to 30%. 

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson