How to override the row level context.

[Chetna_Rath]

I have two Heirarchies, unable to find the sum by category:

I have data something like this:

I need the final table as below:

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table =
SUMMARIZE (
    ADDCOLUMNS (
        Data,
        "@Sum by Category",
            SUMX (
                FILTER ( Data, Data[Category] = EARLIER ( Data[Category] ) ),
                Data[Sales]
            )
    ),
    Data[Sub Category],
    Data[Sales],
    [@Sum by Category]
)

[Chetna_Rath]

Hey. Thanks for the solution, but what if the sales column is in a different table?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Could you please describe how the two tables look like?

[Chetna_Rath]

Hi,

If we had a dimension table - Product

And another fact table containing other KPIs like sales.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Please check the below picture and the attached pbix file.

It is for creating a new table.

New table = 
SUMMARIZE (
    ADDCOLUMNS (
        'Product',
        "@Sales_Subcategory", SUMX ( RELATEDTABLE ( 'Fact' ), 'Fact'[Sales] ),
        "@Sales_Category",
            SUMX (
                CALCULATETABLE (
                    RELATEDTABLE ( 'Fact' ),
                    FILTER (
                        ALL ( 'Product' ),
                        'Product'[Category] = EARLIER ( 'Product'[Category] )
                    )
                ),
                'Fact'[Sales]
            )
    ),
    'Product'[Sub Category],
    [@Sales_Subcategory],
    [@Sales_Category]
)