Thread: DAX measure to create an index

1. DAX measure to create an index

I am trying to create a few DAX measures to work as an indexes for me to plot on a graph.

I did it in Excel with formulas but because I have a number of branches, if I just create a measure called, for example, sum of GPPD index, the only way it works is if I have branch as a row label, and then it treats each branch as its own series. So for example, for Total GP I created a measure of Total Charge and Total Pay, and subtracted Pay from Charge but if I want to do this for indexes it's not as easy.

An added complication is that not all branches have the same starting month.

So I got as far as creating a measure which returns the first month that has a value greater than 0, that works. Where I'm stuck is that I am trying to then return the GPPD for that month as another measure, then the index measure being something like [GPPD]/[Start month GPPD].

If anyone can help or at least point me in the right direction I would be very thankful.

I've attached some data that I'm using. I've tried using CALCULATE and FILTER but just can't get it to work. (The real data has many more branches.)

Alexis  Reply With Quote

2. As it is a linked table, as you said you can do it in Excel, why not just do it in Excel before loading PP?  Reply With Quote

3. That's what I'm doing at the moment, but then in PP I've created the measure as sum of index GPPD. I'm using branch name in a slicer, so if I'm looking at one branch at a time it's fine, but if I do not select a branch and just return the total, or if I want to group 2 or 3 branches together, that's where it doesn't work, because it is 'sum of' and not a proper calculation.

One branch at a time works, more than one doesn't.  Reply With Quote

4. Are you saying that you want to slice on a branch/index combination? If so, just create a concatenated field.  Reply With Quote

Tags for this Thread

dax, dax formula, indexing, powerpivot  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•