Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ras_Ile
Advocate I
Advocate I

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

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.