none
Incorrect result when using a calculated field from another table

    Question

  • Hi,

    Here is my situation

    1. I have written a Calculated Field ("Sessions conducted") formula in a table named "Feedback" to compute distinct sessions conducted.  The formula is

    =DISTINCTCOUNT(Feedback[Date])

    There is a slicer for this table where a person can choose the Type of session (a total of 3 types of sessions) which was conducted.  The choices are MS Excel, VBA and MS Excel, VBA.  I have also dragged Financial year to the column label so I get to know the financial year wise session conducted for the type of session selected.  As and when a slicer selection is made, the "Sessions conducted" figure keeps changing.  So now problem so far.

    2. On another worksheet (different Pivot Table from the first one), I have yet another Calculated Field ("Average realization per session") formula in a table named "Billing" to compute the average realization per session conducted.  The formula is

    =[Total adjusted revenue]/[Sessions conducted]

    There is a slicer for this table where a person can choose the Type of session (a total of 3 types of sessions) which was conducted.  The choices are MS Excel, VBA and MS Excel, VBA only.  I have also dragged Financial year to the column label so I get to know the financial year wise session conducted for the type of session selected.

    Now the problem is that when I select VBA in the slicer of this second worksheet (on the first worksheet, my slicer selected may be a different one, say MS Excel), then the Average realization per session returns an incorrect result.  It returns the result as:

    Total adjusted revenue from VBA (because VBA is selected in the slicer) for a particular financial year/Total sessions conducted for a particular financial year

    So the numerator is correct but the denominator is picking up MS Excel + VBA + MS Excel and VBA sessions conducted.  It should take only the VBA sessions conducted.  Somehow the denominator of the "Average realization per session" Field is not considering the VBA selection in the slicer.

    I think it has to do something with the two calculated Fields being written in different Tables.

    a. The "Sessions conducted" Field is written in the Feedback Table
    b. The "Average realization per session" Field is written in the Billing Table
    c. The "Total Adjusted revenue" Field is written in the Billing Table

    Can you please help me in resolving this issue?

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, June 29, 2013 11:48 PM

Answers

All replies