Create new Calculated Column for an index of Filtered Values

[mrchips]

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!

[v-linhuizh-msft]

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!

[DataInsights]

@mrchips,

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

[mrchips]

Hi @DataInsights and @v-linhuizh-msft , 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 · Accepted solution]

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

-----

[mrchips]

Hi @DataInsights ,

Thank you so much for this, it's really useful. I've had to tweak it a little bit for the following reasons:

- My "Category" does not have a single row in the table, it returns multiple rows so I need to calculate the Cumulative Sum based on that.

- My slicer is Single Select only so I will either have all categories or one, never only a handful.

My measure is now returning a number that is slightly less than that of the budget which changes with different category selection and different budget values, indicating to me that it is doing a correct calculation.

My final question, how can I build Conditional Formatting based off this? Ideally, I would like to change the cell background of the "Included" rows and leave the "Not Included" rows as they are. As it stands, I can't even get the table to not display the rows that aren't included. Do you have any thoughts on this?

Thanks again, 

- 

[DataInsights]

@mrchips,

I prefer to create conditional formatting via measures: it gives you more flexibility and you can reuse logic. Choose format style "Field value" to apply conditional formatting.

If you could share a sanitized pbix (OneDrive, etc.) with the expected result, I'll take a look.

[mrchips]

Hi @DataInsights 

Thanks for the advice, I've created the below Measure which I think does what you were suggesting. However, I am getting the error that it cannot find [@CumulativeCost]. I have done a bit of Googling but can't see how to navigate around the problem in this case.

Budget Filter = 
// Show amount until cumulative sum of amount exceeds budget.
VAR Budget = 'Cost budget'[Cost budget Value]
VAR BaseTable =
    ALLSELECTED ( 'Table'[Name], 'Table'[Index] )
VAR CalcTable =
    ADDCOLUMNS (
        BaseTable,
        "@Amount", 'Table'[Amount],
        "@CumulativeSum",
            SUMX (
                WINDOW ( 1, ABS, 0, REL, BaseTable, ORDERBY ( 'Table'[Index], ASC ) ),
                'Table'[Amount]
            )
    )

RETURN
    SWITCH(
        TRUE,
        [@CumulativeSum] <= Budget, "ORANGE",
        [@CumulativeSum] > Budget, "WHITE"
        )

[DataInsights]

@mrchips,

Are those measures or column references in your measure? Note that in my DAX I use MAX to obtain the budget, and [Amount] is a measure. I don't see [@CumulativeCost] in your DAX. You won't be able to reference [@CumulativeSum] directly in a SWITCH expression because it's a column that contains multiple values.

[mrchips]

Hi @DataInsights ,

- [Name] and [Index] are columns from my original data.

- 'Cost budget'[Cost budget Value] is a measure that takes the SELECTEDVALUE of a slider (So I don't think I need MAX as it already is a scalar)

Cost budget Value = SELECTEDVALUE('Cost budget'[Cost budget])

- [Amount] is a measure as per your code above

I mistyped there, the error was referring to [@CumulativeSum], not [@CumulativeCost]. I was following the example image I have attached below as a guide. I also tried the below code at the bottom of the Budget Filter measure but I am still getting various reference errors.

VAR CalcSum = MAX(CalcTable[@CumulativeSum])

RETURN
    SWITCH(
        TRUE,
        CalcSum <= Budget, "ORANGE")

Example Code from https://blog.coupler.io/power-bi-conditional-formatting/#:~:text=Select%20the%20field%20to%20apply,based%20on%20the%20date%20values.

Is this what you were suggesting with a Measure & Field Value as Conditional Formatting or is there an alternative way?

[DataInsights]

@mrchips,

A best practice you may want to implement is to not precede measure names with a table. This makes measures instantly recognizable (columns are preceded by their table).

Yes, that's the idea with a conditional formatting measure. You can also use hex color codes instead of color names. The temporary column [@CumulativeSum] is visible only with its original measure; you won't be able to reference it from another measure. Note that there are three variables after CalcTable in my DAX, ultimately resulting in a scalar. Are you trying to suppress rows that exceed budget or just highlight them?

[mrchips]

Hi @DataInsights , sorry for the very slow reply.

Understood on the best practice, I will implement that!

I guess I would like a way to do both, but separately for separate things. The highlighting one is my main priority right now as I can already suppress the rows with the measure above.

I am trying to reference [@CumulativeSum] from within the same measure that it is created, not another measure. I had removed the last 3 variables to replace with my own to change the colour but maybe I was mistaken in my implementation.

EDIT: This is currently what I have but it isn't highlightin gthe correct rows at all, not sure what is wrong or whether it is just completely off the mark:

Budget Filter = 
// Show amount until cumulative sum of amount exceeds budget.
VAR Budget = [Cost budget Value]
VAR BaseTable =
    ALLSELECTED ( 'Table'[Corporate Plan / Capital worksheet project name], 'Table'[Index] )
VAR CalcTable =
    ADDCOLUMNS (
        BaseTable,
        "@Amount", [Amount],
        "@CumulativeSum",
            SUMX (
                WINDOW ( 1, ABS, 0, REL, BaseTable, ORDERBY ( 'Table'[Index], ASC ) ),
                [Amount]
            )
    )

VAR CurrentCumulativeSum = 
    CALCULATE(
        MAXX(FILTER(CalcTable, 'Table'[Index] = MAX('Table'[Index])), [@CumulativeSum])
    )
RETURN
    IF(CurrentCumulativeSum > Budget, "ORANGE", "WHITE")

Thanks