I have been working on a model where I have included 3 paramaters to generate a “Master Index” This index is used to prioritize and sort a list of products, based on the above mentioned parameters. I want the users to be able to dynamically change the values of the set of parameters, which in turn will generate the calculated Master Index. I am using this for our Customer Orders where the product numbers are sorted on the Master Index measure, in DESC order. I want to calculate a running total of this generated Index but I have been uncessfull. The order is dynamic so I have no index column and I can not use any chronological column either… The only solution I have devised is to export my visualization (sorted as indicated), then import that csv file and add an index column to this new table!. I am looking to avoid the step of exporting then importing to create a sequenced column and I want to be able to calculate a running total on a measure. I have included 2 images to help display the scenario, any assistance would be greatly appreciated
,Hey Brad, sorry I’m struggling to really understand the probably at the moment.
Are you using the what if parameters to change the parameters?
So are you just looking for a formula that creates a running total, bu you don’t have a date table, rather and index column 1,2,3,4,5 etc
To me the example looks more like a cumulative total based on the index number, would that be right?
Rather than exporting, there may be a way to create a calculated table here that does that. Have you used these before?
Have you got some formulas you can place on here.
And the model…just as much info as possible.
Chrs
I have created calculated tables, but my issue here is in the index that can be used to create the running total. Let me try to be more explicit on the business case.
The business group uses this process to generate part orders for the customer.
There are 3 influential factors that are used to rank the most populart number to the lease popular
- Weighted average of the part sales by the specific customer in a geographic area
- Weighted average of the part sales to all customers in a geographc area
- Weighted average of the Customer Orders for each part number in a geographic area
sry I posted the response without completing the post:
I have 3 paramaters that will allow the business user to change the weighting of each of these 3 factors, when working with the customers. Working with the customer, the business users can flex any of the 3 parameters to determine the best estimate for the appropriate weights, which is a very dynamic process and discussion. When they have come to a consensus on the 3 weights, the "Master Index is used to sort the list of products in a desc manner. I want to be able to generate a running total of this master index. I have no dates that I can use to order the parts, and if I generate at new table with an index, that index is disrupted as the business flex the paramaters, reordering the parts. Is it possible to generate a running total from this measure that calculates this master index
Following up on this same thread, is it possible to create a calculated table which would include the product number, the measure that has generated the master index, and a new index field ?
Ok bit to this one. Let me think about it and try work up a solution
Here’s some logic that gets you the results in a calculated table (remember user would need to refresh the report everytime to get this updated)
Use similar logic to me just add your measures.
First create the first 3 columns
Then use the logic within a calculated column
At the moment I can’t think of any other way to do this dynamically in measures as you need to create the index column in some way.
I use the RANKX function to get the index column based on the ranking.
This works well two as this is somewhat dynamic, say new products came online etc, it would always summarize the entire result.
Let me know if this works.
Think I got it!
Have been working on this one for a while, as I like the tough challenges
Ok I’ll try work through it.
First create an Index table / column like so
Then place it in a table
Then these are the 3 formula…well the structure anyway that get the results in measures. (These are all dynamic…if say in my examples case total sales changes then, everything else will change dynamically - this would be the same for your results I believe)
Below are all measures
The hardest one
Let me know if this works.
Is amazing what you can do with DAX!
THis is perfect, when I am trying to rank the contents of a column like Sales or Total Sales when the column does exist in the table. My situation is that in place of ranking the contents of a column, I want to rank the value of a measure, that does not exist in a column. Here are the steps I go through to get the final index
1:Create 3 Parameters to adjust the weighted values of my 3 components, I’ll call them P1, P2, P3
2: Create a new Calculated column for each of the 3 components of the model, Ill call them C1 product Sales for a specific customer in a geographic region, C2 product sales for all customers in the same geographic region and C3 product orders by the customer. Each of these columns are calculated as the percentage of the grand total of the respective columns
3: Create a new Weighted Measure for each of the 3 components by multiplying them by their appropriate Parameter value, lets call that result W1 = C1* P1, W2=C2P2 and W3= C3P3
4: Create the Master Index by Summing the 3 Weighted measures (W1 + W2 + W3)
This master index is used to sort the products in descending order and this sorted visualization represents the list of products that the Customer will order. The solution provided works exactly how I want in another model where I do have the values that exist within columns of a table. My situation here is that I do not have these columns. I want to Rank the Master Index Measure and becasue of the weighted calculations involved in its evaluation, there is no column in any table that will provide a matching rank or order. I have not been able to create a “virtual” table either because I want this measure as one of the columns, then rank it, but I have been unsuccesful in creating a new table with measures only.
It shouldn’t matter if it’s a column or measure. Once it’s a measure it’s like a virtual calc regardless if it’s initially been calculated from a column or a measure that has many branched before it.
If you sub in the weighted average measure everywhere that I have place my ‘total sales’ it should still all work from my understanding.
What results are you getting when you attempt to do this?
The logic should all be exactly the same, you just need to sub out the total sales for your calc.
By the way that initial [Ranking] measure is the SELECTEDVALUE( RankingIndexColumn)
This may seem a simple question, but how did you create this first index table/column? Is this a manual entry ?
I actually just used the what if parameter, but could do it manually as well, same thing
Hi Sam,
I know this topic is a bit older, but exactly what I need rn. 
When I recreated these measures, I realized, that, if two different products have exactly the same amount of total sales, the “Ranking Index” doesn’t work correctly.
The ranking itself is shown correctly, but the amounts of these two products are summarized.
I guess it won’t happen very often in real life (I’m working with my own data set), but maybe there’s a possibilty to show both products in ranking index? I don’t think there is an additional logic needed, which product is shown first.
…and I hope it’s OK I answered here, instead opening a new topic?
Here’s a screenshot what I mean:
Best wishes from Austria
Greg
hi there,
Standard practice is to start a new topic if the topic is already solved.
You will get better result by creating a new topic as people of the forum don’t look at solve topics.
thanks
Keith