DAX: Dynamic running total calculation scenario

[iloveparting]

Hi

I'm struggling to find a solution for my case and any ideas on how to approach it would be greatly appreciated, thanks in advance.

Consider the following simplified data schema:

ItemRecords Table:

SpendValue (Currency)

VendorID (FK)

Vendors Table:

ID (PK)

Name (String)

I'm using a cumulative total filter by SpendValue for my visual (show items that comprise 100/80/50% of overall SpendValue), by calculating item rank by SpendValue, then cumulative total by rank and rendering items that have a cumulative total less than required percent of the overall total:

M VendorSpend = SUM(ItemRecords[SpendValue])

M Sales = IF([M VendorSpend ] >= 0, [M VendorSpend ],0) - VendorSpend happens to take negative values, I need those entries to have the last rank.

M Rank = RANKX(ALL(Vendors), [M Sales])

M RunningTotal = calculate([M Sales],FILTER(ALL(Vendors),[M Rank]<=maxx(Vendors, [M Rank])))

Every feild above is a measure, because the final report contains other visuals that interact with the omitted part of ItemRecords table and I have to respect all the filtering interactions, so they all have to be recalculated upon filtering.

The problem is, ItemRecord and Vendors tables have 500k and 8k rows respectively, and calculating the running total performs extremely poorly (the other measures execute almost instantly). Unfortunately, I don't even see a way to atleast partially move to calculated columns or snapshot any data, because ItemRecord still also has to be independently filtered by date and categorical hierarchy.

I'm looking for any ideas on how to boost performance of running total calculation by either modifying DAX formula or maybe rearranging the data without making it static.

Thank you

[iloveparting]

Dropping an update in case someone runs into a similar issue.

I was unable to solve the problem after all. Even after reducing the data to just the three required columns (Id, Rank and Value, ordered by rank), PBI is still unable to run the calculation over ~30k rows. I tried using all of the suggested formula variations, including the generated 'Running Total' quick measure.

I wonder if this might have to do with the way PBI treats iterative calculations or something? S3 Standard tier Azure SQL server only takes 5 seconds to run the calculation over the same data with an update statement.

[OpenDataLab]

The problem in your calculation is the use of the IF statement.  The IF statement cannot be executed in the storage engine of the Power BI data model,  which is what make Power BI fast. but in the Formula engine.

When you use the IF statement in a recursive function like MAXX then for each row it will call out to the formula engine, this is incredibly slow.  IF statements are bessed used to validate the execution of your formula, but are not very good when used inside recursive functions.  Not with 500k+ iterations.

There are some very nice design patterns for doing the type of grouping you are looking for.  I will post something a little later.

[iloveparting]

I got rid of that a while ago, it's just three columns of data and the formula now:

 

RunningTotal = 
CALCULATE(
	SUM('CC RT Test'[Value]),
	FILTER(
		ALLSELECTED('CC RT Test'),
		'CC RT Test'[CC Rank]<=MAXX('CC RT Test','CC RT Test'[CC Rank])
	)
)

[Greg_Deckler]

The performance issues are likely due to your use of RANKX and MAXX, which iterate over the entire table. If you can get rid of those, you likely will be far better off. Have you tried to running total quick measure?

[iloveparting]

When creating a running total quick measure, I'm not allowed to aggregate over a calculated field (Rank in my case). I tried changing Rank and Total to calculated columns instead of measures (and using EARLIER instead of MAXX to iterate), in which case it does execute much faster, but sadly vendor ranking order can change after filtering records by date and such.

Do you happen to be aware of any other ways to implement dynamic ranking and iteration with DAX other than RANKX and MAXX for running total?

[v-yuezhe-msft]

Hi @iloveparting,

Please review the formulas I used to create dynamic running total in the following similar threads and check if these formulas work faster in your scenario.

http://community.powerbi.com/t5/Desktop/Production-Pareto-does-not-work-if-select-more-than-1/m-p/17...
http://community.powerbi.com/t5/Desktop/Cumulative-SUM-using-Rank-NOT-Dates/m-p/170623#M74489

Thanks,
Lydia Zhang