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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.