How to set row subtotal be the sum of the underlying row values?

[Ras_Ile]

Hi Community,

I have an issue that I need your guidance on.

I have a fact table [f_Sales] with monthly sales of companies; [Company] - [Year.Month] - [Sales].

I also have a dimension table [d_Companies] for the companies: [Company] - [Company Subgroup] - [DateOfAcquisition] - [DateOfDivestment], where [DateOfAcquisition] represents the date the company was bought and [DateOfDivestment] represent the date the company was sold. If the company is not sold, the value is BLANK().

Finally, I have a date table [d_DateTable], but that is of lesser importance here.

In [f_Sales] table, I have sales data for all the companies. The time-range of the sales go both before [DateOfAcquisition] and, if the company is sold, also after [DateOfDivestment].

I have created a measure [Rolling12MonthSales] that returns the sum of the last twelve months of sales using DATESINPERIOD.

I have also created a second measure [AdjustedRolling12MonthSales] that returns 0 if the company is divested at the time:

VAR DivestmentDate = LOOKUPVALUE(d_Companies[DateOfDivestment],d_Companies[Company],MAX(f_Sales[Company]))

VAR CurrentDate = MAX(d_DateTable[Date])

VAR IsDivested = IF(DivestmentDate=BLANK(),"No", IF(CurrentDate>DivestmentDate,"Yes","No"))

VAR AdjustedRolling12MonthSales= CALCULATE(IF(IsDivested="Yes",0,[Rolling12MonthSales]))

RETURN AdjustedRolling12MonthSales

Now - here is my problem:
When I want to show [AdjustedRolling12MonthSales]in a matrix with dates in the columns and the rows with two levels [Company Subgroup] and [Company]  - the  AdjustedRolling12MonthSales will show correct monthly values for each [Company]. If the company has been sold, that specific column and row will show 0.

But the row subtotal [Company Subgroup] value does not sum up the values for AdjustedRolling12MonthSales , but instead returns regular [Rolling12MonthSales] values, ignoring that the underlying rows contain 0 values for some of the companies for the period. 

How can I make it so that the row subtotal [Company Subgroup] value is indeed showing the subtotal value of the underlying row values? 

[DataInsights · Accepted solution]

@Ras_Ile,

Try this measure:

AdjustedRolling12MonthSales =
VAR AllCompanies =
    SUMMARIZE (
        f_Sales,
        d_Companies[Company],
        d_Companies[Company Subgroup],
        d_Companies[DateOfDivestment],
        d_DateTable[Date]
    )
VAR DivestedCompanies =
    FILTER (
        AllCompanies,
        NOT ISBLANK ( d_Companies[DateOfDivestment] )
            && d_DateTable[Date] > d_Companies[DateOfDivestment]
    )
VAR AdjustedRolling12MonthSales =
    SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN
    AdjustedRolling12MonthSales

[Ras_Ile]

Fantastic - you are a wizard, sir! Thank you!