Forum Discussion
DAX Formula
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 |
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
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
9 Replies
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.
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
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
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 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
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
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.
Got it, adding another column to allexcept.
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
