DAX measure to create an index

Alexis Kiteos

New member
Joined
Sep 9, 2014
Messages
2
Reaction score
0
Points
0
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
 

Attachments

  • Calculate Filter example.xlsx
    194 KB · Views: 72
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?
 
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.
 
Are you saying that you want to slice on a branch/index combination? If so, just create a concatenated field.
 
Back
Top