Prevous year amount will not aggragate in pivot table.

Rob Rayborn 1 Reputation point
2021-10-29T20:25:35.91+00:00

I have a measure: IF([Revenue]=0,-[Previous YR Revenue],[Revenue])
When I pull this measure into a power pivot table with the Year being the Column filter, the -[Revenue Previous Year] does not aggregate as part of the sub total or grand total. The Sub totals and grand totals only aggregate the [Revenue] amount (or positive amount).
How to I write this formula so that the negative amounts "-[Revenue Previous Year]" are included in the sub total and grand totals?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viki Ji_MSFT 4,421 Reputation points
    2021-11-02T08:17:42.317+00:00

    Hi @Rob Rayborn ,
    Thanks for your reply and sahring.

    According to the screenshot you provided, I reproduced your issue on Excel 365.
    Per my research, I found that this is a known issue in Excel. For more information, please refer to: Calculated field returns incorrect grand total in Excel.
    Thanks for your understanding.

    Per my testing, I think the following may be a workaround, please check if it is helpful.
    Try to create a " - Revenue PY" auxiliary column, and then create a new measure of " - Revenue PY + Revenue".

    145723-image.png

    Besides, if you have any suggestions about this issue, it is recommended that you go to File>Feedback in Excel to submit your suggestions to Microsoft directly.
    Thanks.

    Hope the information is helpful to you.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Herbert Seidenberg 1,191 Reputation points
    2021-11-02T19:04:48.583+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.
    Added Calculated Column, not Calculated Field
    that some do not know does not exist in PP.
    Added HasOneValue() in Measures.
    https://www.mediafire.com/file/15iybvgk4krgf0i/11_02_21.xlsx/file
    https://www.mediafire.com/file/49813tjy5kqgqa2/11_02_21.pdf/file

    0 comments