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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Are you saying that you want to slice on a branch/index combination? If so, just create a concatenated field.
 
Top