Sum of sales within a range

[Arnaud31450]

Hi team !

I am trying to get a sum of gross profit within a particular selling price range and then divide them by the sales within that range to get the gross margin.

I have tried to following below but it does not work. error message: "A function placeholder has been used in a True/False expression that is used as a table filter expression".

Sales_List Price to Lvl.1 = 
DIVIDE(
CALCULATE(
    SUM('Fact Sales Actual'[gp_sm_fxb]),
    'Measure AB_1'[ASP_LC_Fxb]<AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c]),'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c])
),
CALCULATE(
    SUM('Fact Sales Actual'[sales_sm_fxb]),
    'Measure AB_1'[ASP_LC_Fxb]<AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c]),'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c])
    )
)

thanks in advance for the help !

[Arnaud31450]

So apparently it does not filter correctly as I did:

GP Lvl.2 to Lvl.3 = 
VAR AvgLevel2   = AVERAGE ( ProductPricing[Range2__c] )
VAR AvgLevel3 = AVERAGE ( ProductPricing[Range3__c] )
VAR PriceRange =
    FILTER (
        'ProductPricing',
        AND (
            AvgLevel2 > [ASP_LC_Fxb],
            [ASP_LC_Fxb] > AvgLevel3
        )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'Fact Sales Actual'[gp_sm_fxb] ),    PriceRange ),
        CALCULATE ( SUM ( 'Fact Sales Actual'[sales_sm_fxb] ), PriceRange )
    )

And still got the same number ...

[AlexisOlson]

I don't know your model but it's possible that the measure [ASP_LC_Fxb] isn't behaving as you think inside the FILTER row context. How is this measure defined?

[Arnaud31450]

ASP_LC_Fxb =

DIVIDE(

    SUM('Fact Sales Actual'[sales_sm_fxb]),

    SUM('Fact Sales Actual'[units sold])

)

[Arnaud31450]

It worked thanks a lot ! 
Need to investigate VAR and renaming variables !

[AlexisOlson]

Yep. It can help make the code more readable sometimes and sometimes even help performance by allowing you to calculate a value that is used repeatedly only once.

[Arnaud31450]

Also tried this which gives syntax error:

Sales_List Price to Lvl.1 = 
DIVIDE(
CALCULATE(
    SUM('Fact Sales Actual'[gp_sm_fxb]),
    FILTER(AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c])>'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c])
)),
CALCULATE(
    SUM('Fact Sales Actual'[sales_sm_fxb]),
    FILTER(AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c])>'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c])
    ))
))

And this:

Sales_List Price to Lvl.1 = 
DIVIDE(
CALCULATE(
SUM('Fact Sales Actual'[gp_sm_fxb]),
FILTER(AND(AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c])>'Measure AB_1'[ASP_LC_Fxb],'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c]))
)),
CALCULATE(
SUM('Fact Sales Actual'[sales_sm_fxb]),
FILTER(AND(AVERAGE('Price List Item'[Apttus_Config2__ListPrice__c])>'Measure AB_1'[ASP_LC_Fxb],'Measure AB_1'[ASP_LC_Fxb]>AVERAGE('Price List Item'[TargetPrice__c]))
))
))

[AlexisOlson · Accepted solution]

The first argument of FILTER has to be the table you are filtering.

Maybe this will get you a bit closer:

Sales_List Price to Lvl.1 =
VAR AvgListPrice   = AVERAGE ( 'Price List Item'[Apttus_Config2__ListPrice__c] )
VAR AvgTargetPrice = AVERAGE ( 'Price List Item'[TargetPrice__c] )
VAR PriceRange =
    FILTER (
        'Price List Item',
        AND (
            AvgListPrice > [ASP_LC_Fxb],
            [ASP_LC_Fxb] > AvgTargetPrice
        )
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( 'Fact Sales Actual'[gp_sm_fxb] ),    PriceRange ),
        CALCULATE ( SUM ( 'Fact Sales Actual'[sales_sm_fxb] ), PriceRange )
    )