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
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:
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?
Solved! Go to Solution.
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
Proud to be a 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]
)
VAR AdjustedRolling12MonthSales =
SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN
AdjustedRolling12MonthSales
Proud to be a Super User!
Fantastic - you are a wizard, sir! Thank you!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
100 | |
98 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |