Forum Discussion

mrchips's avatar
mrchips
Regular Visitor
2 years ago
Solved

Create new Calculated Column for an index of Filtered Values

Hi,

 

I am trying to create a Calculated Column that creates an index based on the Filtered Values. I have an index on my Unfiltered Table and I'm using that to calculate the rank like below:

 

FilteredIndex = 
RANKX(
    ALLSELECTED('MyTable'),
    'MyTable'[Index],
    ,
    ASC,
    DENSE
)

 

 

However, when I select an option in a Slicer to Filter the data, the FilteredIndex column just shows the same as the Index column. I know the slicer is working as the "Table" card updates to only show the selected rows.

 

A gif taken from another forum post showing exactly what I want is below. However, the suggested code in that post gives me a circular reference error.

 

Any help on how to get this workign would be much appreciated!

 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    mrchips,

     

    Try these measures:

     

    Amount = SUM ( 'Table'[Amount] )
    Amount Within Budget = 
// Show amount until cumulative sum of amount exceeds budget.
VAR Budget =
    MAX ( 'Cost Budget'[Cost budget value] )
VAR BaseTable =
    ALLSELECTED ( 'Table'[Category], 'Table'[Index] )
VAR CalcTable =
    ADDCOLUMNS (
        BaseTable,
        "@Amount", [Amount],
        "@CumulativeSum",
            SUMX (
                WINDOW ( 1, ABS, 0, REL, BaseTable, ORDERBY ( 'Table'[Index], ASC ) ),
                [Amount]
            )
    )
VAR FilterTable =
    FILTER ( CalcTable, [@CumulativeSum] <= Budget )
VAR CategoryToInclude =
    SELECTCOLUMNS ( FilterTable, "Category", 'Table'[Category] )
VAR Result =
    CALCULATE ( [Amount], KEEPFILTERS ( CategoryToInclude ) )
RETURN
    Result

     

    You can adjust the Budget variable depending on how you obtain budget in your model.

     

    Sample data:

     

     

     

    Result:

     

    -----

     

     

11 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    mrchips,

     

    Calculated columns are unable to recognize slicer selections. Measures, however, do recognize slicer selections.

    • mrchips's avatar
      mrchips
      Regular Visitor
      2 years ago

      Hi DataInsights and Anonymous , thanks for your advice on this. However, it leads me straight into another issue.

       

      I am using the Index column to try and calculate the Cumulative Sum of a number of rows that I can then compare to a slider value to choose whether to include them or not. (I.e. Iterate through the rows and "Include" them, until the cumulative sum takes me over the budget). This works with the Index column for all the data but I want it to recalculate this if the data is filtered.

       

      Having the Filtered Index as a measure does not allow me to then iterate over the rows and choose what to Include or not. My measures are below:

       

      RunningTotal = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentCost = 'Table'[Cost]
VAR Budget = 'Cost budget'[Cost budget Value]
VAR PreviousTotal = 
    CALCULATE(
        SUM('Table'[Cost]),
        FILTER(
            'Table',
            'Table'[Index] < CurrentIndex
        )
    )
RETURN
IF(PreviousTotal + CurrentCost <= Budget, PreviousTotal + CurrentCost, PreviousTotal)

       

      IncludeFlag = 
VAR CurrentIndex = MAX('Table'[Index])
VAR Budget = 'Cost budget'[Cost budget Value]
VAR CumulativeCostAtCurrentIndex = 
    CALCULATE(
        SUM('Table'[CumulativeCost]),
        FILTER(
            'Table',
            'Table'[Index] = CurrentIndex
        )
    )
VAR NextCumulativeCost = 
    CALCULATE(
        SUM('Table'[CumulativeCost]),
        FILTER(
            'Table',
            'Table'[Index] = CurrentIndex + 1
        )
    )
RETURN
IF(
    CumulativeCostAtCurrentIndex <= Budget &&
    (NextCumulativeCost = BLANK() || NextCumulativeCost <= Budget),
    1,
    0
)

       Any advice on how to work around this would be much appreciated.

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        2 years ago

        mrchips,

         

        Try these measures:

         

        Amount = SUM ( 'Table'[Amount] )
        Amount Within Budget = 
// Show amount until cumulative sum of amount exceeds budget.
VAR Budget =
    MAX ( 'Cost Budget'[Cost budget value] )
VAR BaseTable =
    ALLSELECTED ( 'Table'[Category], 'Table'[Index] )
VAR CalcTable =
    ADDCOLUMNS (
        BaseTable,
        "@Amount", [Amount],
        "@CumulativeSum",
            SUMX (
                WINDOW ( 1, ABS, 0, REL, BaseTable, ORDERBY ( 'Table'[Index], ASC ) ),
                [Amount]
            )
    )
VAR FilterTable =
    FILTER ( CalcTable, [@CumulativeSum] <= Budget )
VAR CategoryToInclude =
    SELECTCOLUMNS ( FilterTable, "Category", 'Table'[Category] )
VAR Result =
    CALCULATE ( [Amount], KEEPFILTERS ( CategoryToInclude ) )
RETURN
    Result

         

        You can adjust the Budget variable depending on how you obtain budget in your model.

         

        Sample data:

         

         

         

        Result:

         

        -----

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mrchips ,

     

    Thank DataInsights  for the right ideas and I will make some additions.

     

    When the CALCULATE function is used, it converts the current row context to a filtering context. If CALCULATE is used in a calculated column and there is no primary key column to uniquely identify each row, a circular dependency may result. Inside your formula, the RANKX function needs to traverse the entire table when calculating the rankings, and each row in this table depends on the FilteredIndex value. This leads to a loop. It is possible to move the computational logic into the measure, as measure do not cause circular dependencies. Please follow the following steps:

     

    1.Create a simple data:

     

     

    2.Create a new measure:

     

     

    FilteredIndex =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Index] ) ),
    ,
    ASC,
    DENSE
)

     

     

    3.The final result is as follows:

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!