Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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
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
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |