Remove Row Total Paginated Report

[amirghaderi]

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?

[amirghaderi]

Thank you, this worked!

[d_gosbell · Accepted solution]

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]

[d_gosbell]

That looks like it's probably being generated by your query. So either remove the code that is generating the total (or show us the query so we can tell you how to do that). Or apply a filter to your table to exclude rows with a blank in the Supplier column. Fixing the query is probably the better option as it means the server will have to do less work to return the results

[amirghaderi]

This is thequery:

// 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(

      ROLLUPADDISSUBTOTAL(

        ROLLUPGROUP(

          '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]

        ), "IsGrandTotalRowTotal"

      ),

      __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]