Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter Criteria on Caluculation

% 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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    % 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    % 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's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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's avatar
      Anonymous
      Not applicable
      8 years ago

      Any  solution for this please.

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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