Forum Discussion
Remove Row Total Paginated Report
- 5 years ago
In the __DS0Core variable you can see the calls to ROLLUPGROUP and ROLLUPISSUBTOTAL functions which are generating this extra total row.
Obviously without your data model I cannot test the query below, but I think I've removed those two functions correctly.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS ( { "Hours" }, 't_PP11'[UOM] )
VAR __DS0FilterTable2 =
FILTER (
KEEPFILTERS ( VALUES ( 'Date'[weekending] ) ),
AND (
'Date'[weekending] >= DATE ( 2021, 5, 14 ),
'Date'[weekending] < DATE ( 2021, 5, 21 )
)
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_Project'[Project Engineer],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending],
__DS0FilterTable,
__DS0FilterTable2,
"SumActual_MH", CALCULATE ( SUM ( 't_PP11'[Actual MH] ) )
)
VAR __DS0PrimaryWindowed =
TOPN (
502,
__DS0Core,
[IsGrandTotalRowTotal], 0,
't_Project'[ Project Engineer], 0,
't_PP11'[Employee or Supplier], 1,
't_class'[Adjusted Exp. Cat. Name], 1,
't_PP11'[Booking Code], 1,
't_Project'[Job Code], 1,
't_Project'[Job Description], 1,
'Date'[weekending], 1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
't_Project'[ Project Engineer] DESC,
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]
In the __DS0Core variable you can see the calls to ROLLUPGROUP and ROLLUPISSUBTOTAL functions which are generating this extra total row.
Obviously without your data model I cannot test the query below, but I think I've removed those two functions correctly.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS ( { "Hours" }, 't_PP11'[UOM] )
VAR __DS0FilterTable2 =
FILTER (
KEEPFILTERS ( VALUES ( 'Date'[weekending] ) ),
AND (
'Date'[weekending] >= DATE ( 2021, 5, 14 ),
'Date'[weekending] < DATE ( 2021, 5, 21 )
)
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_Project'[Project Engineer],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending],
__DS0FilterTable,
__DS0FilterTable2,
"SumActual_MH", CALCULATE ( SUM ( 't_PP11'[Actual MH] ) )
)
VAR __DS0PrimaryWindowed =
TOPN (
502,
__DS0Core,
[IsGrandTotalRowTotal], 0,
't_Project'[ Project Engineer], 0,
't_PP11'[Employee or Supplier], 1,
't_class'[Adjusted Exp. Cat. Name], 1,
't_PP11'[Booking Code], 1,
't_Project'[Job Code], 1,
't_Project'[Job Description], 1,
'Date'[weekending], 1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
't_Project'[ Project Engineer] DESC,
't_PP11'[Employee or Supplier],
't_class'[Adjusted Exp. Cat. Name],
't_PP11'[Booking Code],
't_Project'[Job Code],
't_Project'[Job Description],
'Date'[weekending]