Forum Discussion

amirghaderi's avatar
amirghaderi
Helper IV
5 years ago
Solved

Remove Row Total Paginated Report

I have a paginated report which shows the row total as the first line. I have been trying to remove it from the report and no luck yet. Any idea about it?  
  • d_gosbell's avatar
    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]