Forum Discussion
Table Totals Wrong for Measure
I have burned a ton of hours reasearching and testing posted solutions and cannot seem to get the total on a measure for a Table visualization to come out right. The detail row values are exactly rig...
I found another way to solve this problem by bringing the daily revenue number into the Fact table then doing a simple CALCULATE function to ignore the date slicer on the page as:
Lost Revenue = 0 + CALCULATE(SUM('Fact Daily Inventory'[Daily Revenue]), 'Fact Daily Inventory'[EquipmentStatusCode] IN {"D", "N"}, ALL('Dim Date'[CalendarDate]))Once I did this, the table computed the totals correctly.
Thanks for the reply. That didn't work. It definitely changed the sum, but it still isn't correct.
Would it help if I posted the DAX for that table captured by the performance analyzer. If so, here it is:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"TRASH PUMP GAS 2"" - 4"""}, 'Dim Equipment (Down)'[Cat Class Category])
VAR __DS0FilterTable2 =
TREATAS({"Down < 20 Days",
"Down > 20 Days"}, 'Dim Equipment (Down)'[Status])
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('Dim Date'[CalendarDate])),
AND(
'Dim Date'[CalendarDate] >= DATE(2020, 5, 3),
'Dim Date'[CalendarDate] < DATE(2020, 5, 4)
)
)
VAR __DS0FilterTable4 =
TREATAS(
{("GENERAL TOOL", "EAST TERRITORY", "REGION 03", "CENTRAL DISTRICT", "CHARLOTTE NORTH PC582")},
'Dim Branch'[Specialty Name],
'Dim Branch'[Territory Name],
'Dim Branch'[Region Name],
'Dim Branch'[District Name],
'Dim Branch'[Branch Name]
)
VAR __ValueFilterDM1 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Dim Equipment (Down)'[Asset Number],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"Test", 'Fact Daily Inventory'[Test],
"Days_Down_Count", IGNORE('Fact Daily Inventory'[Days Down Count])
)
),
[Days_Down_Count] > 0
)
EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('Dim Equipment (Down)'[Asset Number], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__ValueFilterDM1,
"Test", 'Fact Daily Inventory'[Test]
),
[IsGrandTotalRowTotal],
0,
'Dim Equipment (Down)'[Asset Number],
1
)
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Dim Equipment (Down)'[Asset Number]
I found another way to solve this problem by bringing the daily revenue number into the Fact table then doing a simple CALCULATE function to ignore the date slicer on the page as:
Lost Revenue = 0 + CALCULATE(SUM('Fact Daily Inventory'[Daily Revenue]), 'Fact Daily Inventory'[EquipmentStatusCode] IN {"D", "N"}, ALL('Dim Date'[CalendarDate]))
Once I did this, the table computed the totals correctly.