SUM values based on rank if summed figure is less than another set value

[ao352]

I would like to sum a range of values in Power BI based on their ranking, if they sum up to an amount below a set value.

For Example: 

The price of the highest ranked items are summed up, until the point whereby the Cumulative Total exceeds the Budget. The other values which do not make the cut would not be summed up, just displayed.

			Budget	£123
	Item	Price (£)	Rank	Cumulative Total
	Ginger	8	1	8
	Cake	32	2	40
	Banana	64	3	104
	Pie	74	4	0
	Strawberries	77	5	0
	Chocolate	89	6	0
	Total	104	-	-

If the budget were to change, so would the Cumulative total. 

			Budget	£180
	Item	Price (£)	Rank	Cumulative Total
	Ginger	8	1	8
	Cake	32	2	40
	Banana	64	3	104
	Pie	74	4	178
	Strawberries	77	5	0
	Chocolate	89	6	0
	Total	178	-	-

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-yuta-msft · Accepted solution]

@ao352 ,

Suppose you have another budget table and the slicer is based on budget table, you can create a measure using DAX below to calculate the cumulative sum which hasn't exceeded the threshold:

Cumlative Total = 
VAR Budget_Value = SELECTEDVALUE(Budget[Budget])
VAR Running_Total = CALCULATE(SUM('Table'[Price]), FILTER(ALL('Table'), COUNTROWS(FILTER('Table', 'Table'[Rank] >= EARLIER('Table'[Rank])))))
RETURN
IF(Running_Total <= Budget_Value, Running_Total, 0)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ao352]

Thank you! 🙂