Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dunnobe
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors