locked
How does this CALCULATE function formula in a Calculated Column know to use Row Context? RRS feed

  • Question

  • Dear Team,

    I am creating a Calculated Column to simulate VLOOKUP Approximate Match. The goal of the formula is to retrieve the Cost discount based on number of units sold. I have a table named “disCostDiscounts”, where the first column is Units and the second column is Discounts. The table looks like this:

    Units

    Discounts

    0

    0

    26

    0.02

    51

    0.03

    76

    0.05

    100

    0.075

    500

    0.1

    1000

    0.15

    I also have transactions table named “fSales”, where one of the columns is named Units.

    Here is my Calculated Column formula in the “fSales” table:

    =CALCULATE(MAX(disCostDiscounts[Discounts]),FILTER(disCostDiscounts,disCostDiscounts[Units]<=fSales[Units]))

    My question are:

    1. Can you explain the step-by-step calculating process for this formula?
    2. The part that I am not understanding is this: I thought that inside the CALCULATE function, CALCULATE always ran “Filter Context”, so how is it that the formula knows that for “fSales[Units]” it should be doing “Row Context”? Does the fact that the formula is in a Calculated Column which automatically creates “Row Context” override the “Filter Context” inside CALCULATE? Or is this a case where CALCULATE does its “Context Transition”?

    Sincerely, Mike "Trying To Learn PowerPivot, but it is Really Hard" Girvin

    Sunday, August 24, 2014 8:54 PM

Answers

  • In a table (whether a calculated column in a physical table, or in a table function, e.g. with ADDCOLUMNS() ), CALCULATE transforms row context into filter context which can then be modified based on arguments 2 - N of CALCULATE().

    In a pivot, CALCULATE internalizes all external filter context (rowfilters, columnfilters, table filters and slicers) to calculate the value in a specific pivot cell.

    • Proposed as answer by Michael Amadi Thursday, October 2, 2014 3:13 PM
    • Marked as answer by Michael Amadi Monday, October 13, 2014 12:22 PM
    Thursday, October 2, 2014 3:05 PM

All replies

  • Mike, is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, October 1, 2014 11:52 PM
  • In a table (whether a calculated column in a physical table, or in a table function, e.g. with ADDCOLUMNS() ), CALCULATE transforms row context into filter context which can then be modified based on arguments 2 - N of CALCULATE().

    In a pivot, CALCULATE internalizes all external filter context (rowfilters, columnfilters, table filters and slicers) to calculate the value in a specific pivot cell.

    • Proposed as answer by Michael Amadi Thursday, October 2, 2014 3:13 PM
    • Marked as answer by Michael Amadi Monday, October 13, 2014 12:22 PM
    Thursday, October 2, 2014 3:05 PM