Forum Discussion

f1254lipm's avatar
f1254lipm
Frequent Visitor
3 years ago

Bottom 10th product sales amount value optimization

Hi, I need a little bit of help to optimize a measure that always gets the 10th bottom product sales amount and reacts to only specified filters. I created the measure on sample columns and data so I can show the current measure definition. The measure is used for example in a table visual where only the top 5 customers by sales amount are filtered but I don't want the 10th bottom product sales amount value to be affected (filtered) by the top 5 customers filter or any other filters with the exception of a filter that I defined in the measure (in this case Education) because the value of the measure is then used in another measure where I add this value to value of other simple sum measure. Is there a way to optimize this measure and at the same time keep the described behaviour.

Thanks

 

The measure:

Nth-Product Sales Amount Dense =
VAR ReferenceProducts = ALL ( DimProduct[ProductKey])
VAR ProductsAndSales =
    ADDCOLUMNS (
        ReferenceProducts,
        "Rank", RANKX (
            ALL ( DimProduct[ProductKey]),
            CALCULATE(CALCULATE([SalesAmmount],ALL(FactInternetSales),VALUES(DimProduct[ProductKey]),VALUES(DimCustomer[EnglishEducation]))),
            CALCULATE(CALCULATE([SalesAmmount],ALL(FactInternetSales),VALUES(DimProduct[ProductKey]),VALUES(DimCustomer[EnglishEducation]))),
            ASC,
            Dense
        )
    )
VAR FilterNthProduct = FILTER ( ProductsAndSales, [Rank] = 10 )
VAR NthAmount = MINX ( FilterNthProduct, CALCULATE(CALCULATE([SalesAmmount],ALL(FactInternetSales),VALUES(DimProduct[ProductKey]),VALUES(DimCustomer[EnglishEducation]))))
RETURN NthAmount

 

1 Reply