PDA

View Full Version : DAX measure to create an index



Alexis Kiteos
2014-09-09, 02:28 PM
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.)

Thanks in advance.

Alexis

Bob Phillips
2014-09-09, 07:46 PM
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?

Alexis Kiteos
2014-09-10, 08:58 AM
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.

Bob Phillips
2014-09-10, 12:16 PM
Are you saying that you want to slice on a branch/index combination? If so, just create a concatenated field.