Filter Criteria on Caluculation

[Anonymous]

% Of Sales By SubCategory =

DIVIDE([_TotalSales],CALCULATE([_TotalSales],ALL(DimProductSubcategory[ProductSubcategoryName]) ),0)*100

here i would like to Filter only records whose % is more than 10%

can some one rewrite the expression for me

[Anonymous]

% Of Sales By SubCategory = 
VAR SalesBySubCat=CALCULATE([_TotalSales],ALL(DimProductSubcategory[ProductSubcategoryName]))
VAR PercentOfSale =DIVIDE([_TotalSales],SalesBySubCat,0)*100
Return 
PercentOfSale>10

//here from below screen shot i want only true records to be displayed.

[Anonymous]

Any  solution for this please.

[Stachu]

if you want to use the DAX filter you will also need to put it in the [SalesAmount] as well - otherwise it will give you blank for the % measure, and amounts for the [SalesAmount]
@Anonymous solution prevents that
Otherwise I would try with something like:

VAR SalesBySubCat=CALCULATE([_TotalSales],ALL(DimProductSubcategory[ProductSubcategoryName]))
VAR PercentOfSale =DIVIDE([_TotalSales],SalesBySubCat,0)*100
VAR SubCategoryFilter = FILTER(ADDCOLUMNS(SUMMARIZE('DimProductSubcategory',DimProductSubcategory[ProductSubcategoryName]),"Val",PercentOfSale),[Val]>10)
RETURN 
CALCULATE(DIVIDE([_TotalSales],SalesBySubCat,0)*100,SubCategoryFilter)

idea is to 'summarize' current subcategory by PercentOfSale and filter out those that do not meet the criteria and then use that as a CALCULATE parameter
Also you cannot use the PercentOfSale cause it will always return value from before you have filtered

[Anonymous]

Have you tried placeing the [% Of Sales By SubCategory] into your report filters and setting it to only show "True"?

[Anonymous]

Thanks Ross for reply,actually i dont want to fiter it by slicer or Default filters,but to filter using DAX expression on caluculated result set.

[Anonymous]

% Of Sales By SubCategory = 
VAR SalesBySubCat=CALCULATE([_TotalSales],ALL(DimProductSubcategory[ProductSubcategoryName]))
VAR PercentOfSale =DIVIDE([_TotalSales],SalesBySubCat,0)*100
Return 
PercentOfSale>10

here instead of retruning True or False i want only True records to be fetched,False should be excluded in result.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can modify your formula to return tag, then drag it to visual level filter to filter unmatched records:

% Of Sales By SubCategory =
VAR SalesBySubCat =
    CALCULATE (
        [_TotalSales],
        ALL ( DimProductSubcategory[ProductSubcategoryName] )
    )
VAR PercentOfSale =
    DIVIDE ( [_TotalSales], SalesBySubCat, 0 ) * 100
RETURN
    IF ( PercentOfSale > 10, 1, 0 )

Regards,

Xiaoxin Sheng