Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Euro0681
Helper II
Helper II

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 that are the same (regardless of additional columns or fields added).

Euro0681_0-1669679857513.png
I've used a measure and have gotten the baseline value which give me correct values but doesn't remain static (as measure work with filter context) so I have tried adjusting the calculation to work with a column and it return incorrect values (since they work differently with row context) if someone could suggest a solution! my calculation is below when using the measure

Baseline = 
Calculate ( DIVIDE (SUM ('Table'[Amount]), SUM ('Table'[Quantity]) ), 'Table'[Period] in {"2019 - 11", "2019 - 12", "2020 - 01"} )

if adding any other columns (fields) such as country or whatever the case is I need this value to stay static for only each product (in a sense like a group by "Product" in SQL if it helps any)


 



1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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.

veasonfmsft_0-1669704023078.png

Best Regards,
Community Support Team _ Eason

View solution in original post

6 REPLIES 6
v-easonf-msft
Community Support
Community Support

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.

veasonfmsft_0-1669704023078.png

Best Regards,
Community Support Team _ Eason

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

@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) 

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?)

FreemanZ
Super User
Super User

try to add a ALL() argument, like this:

Baseline =
Calculate ( 
    DIVIDE (SUM ('Table'[Amount]), SUM ('Table'[Quantity]) ), 
    'Table'[Period] in {"2019 - 11", "2019 - 12", "2020 - 01"},
    ALL() 
)

Didin't work as it only gives me one value thats static for all products and i need static values for each product (meaning product1 = 0.2 , product2 = 0.3 so anytime product1 is displayed it should show 0.2 and anytime product2 is shown it should show 0.3) by using your suggestiion i get one value like 8.2 for all products

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors