Forum Discussion

DianaProcesos's avatar
DianaProcesos
Copper Contributor
Aug 28, 2021

Pivot Table Gran Total

Hi Everyone¡.

 

Thanks a lot for any support that you can give me....   Do you know if it is possible to add some formulas or change the condition to sum to get the gran total in a pivot table?, as you know the values of gran total come from the sum of one field in all items,  but in my case all the data works for me but in the line highlighted in gray I need just one value ( the value that comes from dispatch of tubería 2 item)  not the sume of the field dispatch in Tubería 1 + Tubería 2.

 

Can you suggest something to me?,  

 

Thanks a lot for your help.

 

Regards,

 

 

Diana 

BI & Data Analysis
Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 28, 2021

    DianaProcesos 

    You may

    1) use Power Query to query the range, replace values with condition on null and return query back as PivotTable

    2) creating PivotTable add data to data model, add DAX measure to it like

    Dispatch/Exports TLU2/Withdrawn Volumenes Adj:=IF (
    AND ( ISFILTERED ( Range[System] ), VALUES ( Range[System] ) = "Tuberia 2" ),
    BLANK (),
    CALCULATE (
        SUM ( Range[Dispatch/Exports TLU2/Withdrawn Volumenes] ),
        Range[System] <> "Tuberia 2"
    )
)

    and use it in PivotTable instead of implicit column aggregation.

     

    Bot PivotTables are in attached file, in both filtering value is hardcoded.

Resources