Running total - # of order lines

[dunnobe]

Dear all,

I'm quite new to DAX/PowerPivot and am looking for some advices on a calculated field.

I would like to show per supplier the number of purchases that we have done, sort the from high to low and make a cumulative count (in percentage) from our top suppliers.

I have already made a measure for:

• Sum of all purchases per supplier: Sum NettoBW:=SUM(fPurchase[Nettobestelwaarde])
• Count of purchase orders: Count Inkoopdocument:=COUNT(fPurchase[Inkoopdocument])
• Running total in percentage: ????????????????

I've been googling, but most running totals seems to be different (sum of # of sales, or over time)

Here is what it should look like and my powerpivot data below:

Thank you for your advice.

Dunnobe

[Anonymous]

The name "Row Labels" in the pivot table does not help. Without knowing what the column actually holds, it's not possible to write the measure you want.

Best
Darek

[dunnobe]

Hey @Anonymous ,

Information

"Row Labels" -> name of our suppliers. ->  [Leverancier/leverende vestg.] in the data model

"Count Inkoopdocument" -> count of all orders -> [Inkoopdocument] in the data model. This column in the data model doesn't show quantities, but it's just a column with the names of the purchase orders.

"Sum Nettobestelwaarde" -> sum of the value of all orders per supplier -> [Nettobestelwaarde] in the data model.

I have ranked the "Count Inkoopdocument" from high to low and would like to create a RT/Cumulative % (last column in the first screenshot) based on that order.

I've tried googling, but most of the people are making a RT with a column that holds quantities or financial values, I just wanted to calculate how many rows in my data model.

Should I have an extra table with a countrows per supplier to perform the calculation?

Sorry, I've only been using the data model and PowerPivot for 2 weeks. 😄

[avanderschilden]

Hello,

I think I found the measure you are looking for in one of my old pbix files;

CumulativePercentage = 
VAR __StockItemRank = RANKX(ALL('Warehouse StockItems'[StockItemName]),CALCULATE(SUM('Sales OrderLines'[SalesAmount])))
VAR __CumulativeSalesAmount = SUMX(TOPN(CALCULATE(__StockItemRank), ALL('Warehouse StockItems'[StockItemName]),CALCULATE(SUM('Sales OrderLines'[SalesAmount]))),CALCULATE(SUM('Sales OrderLines'[SalesAmount])))
VAR __SalesAmountAll = CALCULATE(SUM('Sales OrderLines'[SalesAmount]),ALL('Warehouse StockItems'[StockItemName]))
VAR __CumulativePercentage = DIVIDE(__CumulativeSalesAmount,__SalesAmountAll,BLANK())
RETURN __CumulativePercentage

Regards,

Adrian

[Anonymous]

Mate, for running totals you have to have an order defined among the rows/documents. Usually, running totals are calculated along the time axis that has a natural order. I can't see a column by which your rows are ordered... well, actually I can. Is it according to [Count Inkoopdocument] in decreasing order?

Best
Darek