DAX remove grand total and total rows

[jhowe1]

Hi all,

I have a power bi query using a hierarchy, from performance analyzer that I want to use in paginated (SSRS). However using functions SUMMARIZECOLUMNS/ROLLUPADDISSUBTOTAL/ROLLUPGROUP I seem to be forced into having extra rows for grand total/subtotals, and my sums in paginated are double counting. How do I remove them from my query so they are filtered out before getting to paginated/ssrs? Thanks.

EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL (
        ROLLUPGROUP ( 'Sector'[Sector10kL1] ),
        "IsGrandTotalRowTotal",
        'Sector'[Sector10kL2],
        "IsSector10kL2Total",
        'Sector'[Sector10kL3],
        "IsSector10kL3Total",
        'Sector'[Sector10kL4],
        "IsSector10kL4Total",
        'Sector'[Sector10kL5],
        "IsSector10kL5Total"
    ),
    RSCustomDaxFilter(@PeriodPeriod,EqualToCondition,[Period].[Period],Int64), 
    RSCustomDaxFilter(@LegalEntityLegalEntityName,EqualToCondition,[LegalEntity].[LegalEntityName],String),
    "BookValue", 'AllMeasures'[BookValue]
)
ORDER BY ( Sector[Sector10kL1] )

[Anonymous]

Hi @jhowe1 ,

Could you please provide a screenshot of how the data is currently displayed in the paginated report currently? All of the data are from the table Sector? Before sharing, please mask the sensitive data. Is it possible to consider removing the grouping obtained using ROLLUPADDISSUBTOTAL & ROLLUPGROUP and refer the function in the document below to get the total values?

Add a total to a group or tablix in a paginated report (Report Builder)

SSRS Reports: Getting totals and subtotals of unique values from inside groups

Best Regards

[amitchandak]

@jhowe1 , can share column and final outpur you need.

I think you need to remove ROLLUPADDISSUBTOTAL

[jhowe1]

I need to have some grouping within summarizecolumns as i'm using a hierarchy, if there is not a value at every level the row is hidden. It will not allow me to remove ROLLUPADDISSUBTOTAL and keep ROLLUPGROUP