CALCULATE trouble

[Misca]

Here's my basic Power Pivot data model:


The Sales table has basic transactions data: CustomerID, Product ID, Sales Amount etc. and is connected to the Products -table via Product ID and Customers table with the Customer ID. These tables are connected to the CustomerGroups and ProductGroups tables with GroupIDs.


How do I write a CALCULATE -measure that tells me the NetSales of each ProductGroup for each CustomerGroup even when the pivot table is showing the ProductIDs and CustomerIDs (ie. just the subtotals but for each row)? All the CALCULATE examples seem to have either static values or the SAMEPERIODLASTYEAR-function as the filter values but I'd just need the "level up" values.

 

[Ozeroth]

Hi there,

Just focussing on Products first, this is what I think you want to do - does this sound correct ?

"For Products currently visible in the Sales table, calculate the total sales for all Products in the corresponding ProductGroups (with filters other than Products table left unchanged)."​

One measure to achieve this would be (I'm assuming Sales[Net Sales] is a column):

=
CALCULATE (
    SUM ( Sales[Net Sales] ),
    ALL ( Products ),
    SUMMARIZE ( Sales, ProductGroups[GroupID] )
)

A similar pattern would apply for Customers.

To "level up" Products/Customers simultaneously:

=
CALCULATE (
    SUM ( Sales[Net Sales] ),
    ALL ( Products ),
    ALL ( Customers ),
    SUMMARIZE ( Sales, ProductGroups[GroupID], CustomerGroups[GroupID] )
)

Do these behave as expected in the pivot table you were talking about?

 

[Misca]

Thank you!

You nailed it: The second formula was just what I was looking for.