Forum Discussion
Using measures based on filtered data in a calculated column
Hi colec6 ,
These's no filter condition in your measure so you can simply use this pattern:
Subtraction_measure = MINX ( TABLE1, TABLE1[Value] ) - [Average_To_Subtract]
Regards,
Jimmy Tao
Jimmy,
I think you may be on to something here with your comment about a subtraction error. I had originally tried to just subtract the average measure from the column. I implemented a subtrac tion measure using the following:
Subtraction_measure = CALCULATE(MINX,TABLE1,TABLE1[Value]) - [Average_To_Subtract]
I then plotted this out and this does indeed appear to do what I wanted it to do.
A follow on question though - In my head I had measures as individual values - am I right in thinking that this is true and that the use of the MINX in the CALCULATE statement then applies this subtraction to the minimum value (effectively the only value) in each row of the value column in table1?
Chris
Community SupportHi colec6 ,
These's no filter condition in your measure so you can simply use this pattern:
Subtraction_measure = MINX ( TABLE1, TABLE1[Value] ) - [Average_To_Subtract]
Regards,
Jimmy Tao
Jimmy,
Thanks ... The subtraction measure did the trick. For anyone who is looking for the solution here is what I did:
1 - Calculate an average measure for the data applying filters in table 2 to table 1 data
CALC_AVG = CALCULATE(AVERAGE(TABLE1[VALUE]),FILTER(ALL(TABLE1),TABLE1[CATEGORY_A]=FILTERS(TABLE2[CATEGORY_A]) && TABLE1[CATEGORY_B]=FILTERS(TABLE2[CATEGORY_B])))
2 - Calculate a subtraction measure
CALC_SUBTRACTION = MINX(TABLE1,TABLE1[VALUE])-[CALC_AVG]
I could then use a combination chart to plot bars from the values selected in table 1 and an average line using the CALC_AVG measure
I then plotted the subtraction measure on a seperate bar chart which gave me the difference between the average from table 2 using the filters on table 2 and the values from table 1