topic Re: Get the value for sub category by certain % in DAX Commands and Tips

Get the value for sub category by certain %

Anonymous — Thu, 05 Jun 2025 08:11:17 GMT

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.

Re: Get the value for sub category by certain %

SamsonTruong — Thu, 05 Jun 2025 08:34:41 GMT

Hi Anonymous ,

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

Re: Get the value for sub category by certain %

johnt75 — Thu, 05 Jun 2025 09:20:37 GMT

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