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,

 

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's avatar
mrchips
Regular Visitor
2 years ago

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