cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Running total - # of order lines

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:

Capture.JPGpowerpivot.JPG

 

Thank you for your advice.

 

Dunnobe

4 REPLIES 4
Anonymous
Not applicable

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

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. 😄

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
Not applicable

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.