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:

     

    -----

     

     

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:

     

    -----

     

     

    • mrchips's avatar
      mrchips
      Regular Visitor
      2 years ago

      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's avatar
        DataInsights
        Icon for Super User rankSuper User
        2 years ago

        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.