Forum Discussion

Euro0681's avatar
Euro0681
Icon for Helper II rankHelper II
3 years ago
Solved

Static Baseline Value (Grouped on Category ID)

I'm trying to Calculate a baseline value which calculates a products weighted price by calculating "SUM(Amount)/SUM(Quantity)" at a given point in time but the value must remain static for all items ...
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi， Euro0681 

    You should try ALLEXCEPT ,like this:

    Baseline = 
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Amount] ), SUM ( 'Table'[Quantity] ) ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ProductID] ),
        'Table'[_Period] IN { "2019 - 11", "2019 - 12", "2020 - 01" }
    )
)

    Please check the sample file for more details.

    Best Regards,
    Community Support Team _ Eason

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi， Euro0681 

You should try ALLEXCEPT ,like this:

Baseline = 
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Amount] ), SUM ( 'Table'[Quantity] ) ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ProductID] ),
        'Table'[_Period] IN { "2019 - 11", "2019 - 12", "2020 - 01" }
    )
)

Please check the sample file for more details.

Best Regards,
Community Support Team _ Eason

  • Euro0681's avatar
    Euro0681
    Icon for Helper II rankHelper II
    3 years ago

    This is exactly what I'm needing but one question what if my Period column is coming from a different table than the product ID? (so for simplicity of my question i put them in the same table but i Have a date table where i'm pulling the period from so how would i adjust the all except logic?)

  • Euro0681's avatar
    Euro0681
    Icon for Helper II rankHelper II
    3 years ago

    v-easonf-msft , when replicating this logic it gives me different values the moment i drag in a column that makes the products id repeat? idk if it has to do with the all except since product ID is also coming from product table (so i have 2 table (dimensions) Product and Date then i have my (fact) table connecting to both) 

  • Euro0681's avatar
    Euro0681
    Icon for Helper II rankHelper II
    3 years ago

    v-easonf-msft After Modifying the Calculation you provided I got it to work thanks! You can Ignore my previous replies. (Marked as solution)