Using measures based on filtered data in a calculated column

[colec6]

Hello all.

I'm really hoping that someone can help me with this as I've got to the banging head against wall stage.

I have 2 tables containing data the tables are not linked in the data model.

The tables are clones of each other and have 3 columns, 2 categorical and one numerical.

What I am trying to do is to allow the user to select a primary data set using filters in one table (i.e. category A = 1, Category B = 'a' 

I also want the user to be able to select a secondary data set in the second table (again using the filters for that table) (i.e. Category A = 2, Category B = 'c').

I then want to calculate an average value from the filtered results in the table 2 value column and use this in a calculated column in table 1 where I am subtracting this average value from the values in the value column of table 1 to get a difference.

To do this I have been using a measure that looks like this :

Average_to_subtract = CALCULATE(AVERAGE(TABLE1[VALUE]),FILTER(ALL(TABLE1),TABLE1[CATEGORY1]=FILTERS(TABLE2[CATEGORY1]) && TABLE1[CATEGORY2]=FILTERS(TABLE2[CATEGORY2])))

As a note I have also tried to do this using the measure below as well (i.e. tring to get the average from the second table rather than re-filtering the data in the first table but I get the same results)

Average_to_subtract = CALCULATE(AVERAGE(TABLE2[VALUE]),FILTER(ALL(TABLE2),TABLE2[CATEGORY1]=FILTERS(TABLE2[CATEGORY1]) && TABLE2[CATEGORY2]=FILTERS(TABLE2[CATEGORY2])))

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.

Having read the literature for filter and calculate I had thought that filter returned a filtered table that was then averaged by calculate.  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 as the filtering context is now for the whole table.

Thanks in advance 

Chris

[v-yuta-msft]

Hi 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

[colec6]

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 · Accepted solution]

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

[colec6]

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

[colec6]

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