Forum Discussion
Create new Calculated Column for an index of Filtered Values
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 ResultYou can adjust the Budget variable depending on how you obtain budget in your model.
Sample data:
Result:
-----
Super User
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.
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
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?
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