cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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"))

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?

1 ACCEPTED SOLUTION
Super User

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]
)
SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN

Proud to be a Super User!

2 REPLIES 2
Super User

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]
)
SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN

Proud to be a Super User!

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.