DAX Formula

[chris_steins]

I'm looking to lookup a value in a data table and make a calculation based on it.

Below is my example table, where I'm trying to calculate the "percent Change" column where I want to look up in the table the same "Person" and same "Location" with a Rate of "0" and calculate the percent Change of "Value".  In the first row, it's simply dividing by itself since it has a rate of 0, but the next one it is taking the value (6) , and searching for the Value for the same location and Person and a Rate of 0 (Value of 5).  The final number here would then be subtracted from 1 to get the true value.

Any help would be appreciated, I'm struggling with the ability to lookup the value based on multiple columns and using it to calculate.

Example Data Table:

Person	Rate	Value	Location	percent Change
A	0	5	UN	5/5=1
A	1	6	UN	6/5=1.2
A	2	4	UN	4/5=0.8
A	0	6	VD	6/6=1
A	1	7	VD	7/6=1.2
A	2	5	VD	5/6=0/8

[AlB · Accepted solution]

@chris_steins 

percent Change = 
VAR num_ = Table1[Value]
VAR denom_ =
    CALCULATE (
        AVERAGE ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Location] ),
        Table1[Rate] = 0
    )
RETURN
      DIVIDE ( num_, denom_ ) 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@chris_steins 

So is it solved now?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[chris_steins]

Got it, adding another column to allexcept.

[chris_steins]

Thoughts on what I need to add so that it only looks at the values coming from the same Parent?  Right now it is averaging all Parents with a Rate of 0.

[AlB · Accepted solution]

@chris_steins 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@chris_steins 

percent Change = 
VAR num_ = Table1[Value]
VAR denom_ =
    CALCULATE (
        AVERAGE ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Location] ),
        Table1[Rate] = 0
    )
RETURN
      DIVIDE ( num_, denom_ ) 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[chris_steins]

The suggested code gives me the error of multiple values was supplied where a single value was expected.

Probably because of my followup where there are multiple lines that need averaged.

Another example table.

Person	Rate	Value	Location
A	0	5	UN
A	1	6	UN
A	2	4	UN
A	0	6	VD
A	1	7	VD
A	2	5	VD
A	0	8	UN
A	1	5	UN
A	2	6	UN
A	0	7	VD
A	1	8	VD
A	2	9	VD
B	0	8	UN
B	1	7	UN
B	2	5	UN
B	0	9	VD
B	1	8	VD
B	2	7	VD
B	0	4	UN
B	1	5	UN
B	2	2	UN
B	0	8	VD
B	1	6	VD
B	2	8	VD

[AlB]

@chris_steins 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @chris_steins 

Try this for your calc column

percent Change =
VAR num_ = Table1[Value]
VAR denom_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Location] ),
        Table1[Rate] = 0
    )
RETURN
     ( DIVIDE ( num_, denom_ ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[chris_steins]

One more caveat to the question, the data table will have more than 1 entry with the same Person, Rate, and Location so I want to average the "Values" to use in the equation.