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
Community SupportHi colec6,
Two points should be checked.
"This calculates fine and shows the correct values in cards etc but when I try to create a column where I subtract this value from the existing value in table 1 I get the error : A table of multiple values was supplied where a single value was expected. "
<-- Could you post your "Substraction Measure"?
"If I remove the filtering term and simply subtract a simple measure like measure = AVERAGE(TABLE2[VALUE]) then the calculation works fine but the value is wrong "
<-- What's your expected result?
Regards,
Jimmy Tao
Jimmy,
Thanks for your reply here. The subtraction is not a measure. Rather I took the average measure as posted in the original thread and used it in to calculate a column value where I take the average value measure as calculated from the second table and then subtract it from the value column in the first table - simply TABLE1[Value] - Average_to_subtract.
The expected result is as shown in the blue column in the dummy data. I.e. I calculate an average measure based off of a filtered selection in table 2 and then subtract this value from all the values in Table1[Value] column.
Chris
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
- v-yuta-msft
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,
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
