The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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
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
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
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |