Aggregation in Calculated Column

[AlexNYExcel]

Forgot how wrote formula that able to calculate including Group from Row-level - but I need it ASAP.

Example, we have Sales data of bikes and table with products
there are some columns: bike id, bike model, # of units sold, price, bike group (ex. mountain, kids, etc.).
I am looking for calculated column that will show same values for bike group.
Hurdle that we connect sales data to product by using bike id, and there is nothing in sales data what will show us bike group (for e.g. to USERELATIONSHIP).

hope you will help

 

[Ozeroth]

Hi there,
From what you've said, you want to add a calculated column to your Sales table that gives you, on each row, the total for the current row's Bike Group (which is sitting in the related Products table). Is that right?

Something like this should work as a calculated column in Sales (adapted to your table/column names etc):

=
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    ALL ( Sales ),
    CALCULATETABLE ( VALUES ( Products[Bike Group] ) )
)

• Replace SUM ( Sales[Sales Amount] ) with whatever calculation you want from your Sales table.
• ALL ( Sales ) clears all filters from the current row; this could be changed to ALLEXCEPT( Sales, ...) to leave certain filters in.
• CALCULATETABLE ( VALUES ( Products[Bike Group] ) ) adds a filter on Bike Group corresponding to the current row.

 

[AlexNYExcel]

Hi there,
From what you've said, you want to add a calculated column to your Sales table that gives you, on each row, the total for the current row's Bike Group (which is sitting in the related Products table). Is that right?

Something like this should work as a calculated column in Sales (adapted to your table/column names etc):

=
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    ALL ( Sales ),
    CALCULATETABLE ( VALUES ( Products[Bike Group] ) )
)

• Replace SUM ( Sales[Sales Amount] ) with whatever calculation you want from your Sales table.
• ALL ( Sales ) clears all filters from the current row; this could be changed to ALLEXCEPT( Sales, ...) to leave certain filters in.
• CALCULATETABLE ( VALUES ( Products[Bike Group] ) ) adds a filter on Bike Group corresponding to the current row.

thank you, it is exactly what I was searching.
Actually I wanted put column in Products table, to assign to groups Sales Bracket (e.g. 100m+, 10m+, 1m+, etc.)
but it was easy to adjust