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:   ...
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:

     

    -----

     

     

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!