Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
amirghaderi
Helper IV
Helper IV

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?

amirghaderi_0-1621576039819.png

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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]

View solution in original post

4 REPLIES 4
amirghaderi
Helper IV
Helper IV

Thank you, this worked!

d_gosbell
Super User
Super User

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
Super User
Super User

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

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]

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.