Create measure that ignores filters in a visual

[pbiOP1]

Hello!

I am trying to create a chart that represents two series, but one of the series needs to represent all values in the table, regardless of whether those values are present or not in the other series.

My data come from two tables. The first one has a breakdown of expected income per office for each quarter of the year. Only one instance of each office is represented in this table, with a column per quarter, such as the example below:

TableA
Office	IncomeObjectiveQ1	IncomeObjectiveQ2
A	10	10
B	10	10
C	10	10
D	10	10
E	10	10

The other table has instances of the contracts signed throughout the year, with a code that links each contract and its amount with the office that it belongs to:

TableB
Contract	Office	Income	Date
A1	A	5	Q12019
B1	B	5	Q12019
A2	A	5	Q12019
C1	C	5	Q12019
D1	D	5	Q12019
E1	E	5	Q12018

I want to show the total amount for the contracts signed during the current year, so I have a visual level filter on 'TableB'[Date].

My goal is to have a clustered bar chart, with one bar showing the total amount signed for 2019, and another bar showing the total objective for each quarter of 2019. However, the chart I have does not aggregate any data belonging to Offices that do not have contracts signed during the current year to the Total Objective bar, which means the figure on that bar remains inaccurate (in the example provided, the bar wouldn´t aggregate the 10 belonging to Office E, as that Office does not have any contracts signed during 2019).

So far, I have tried creating a measure using

CALCULATE(SUM('TableA'[IncomeObjectiveQ1]); FILTER(ALL('TableA'[Office])))

or variations of it (adding to the FILTER all columns on TableA, all columns of TableB, combinations of both)... to no avail: the Total Income Objective bar only shows data for offices that have contracts signed within the current year.

Can anyone tell me what am I doing wrong? I am fairly new to PBI, so I am sure this is easily solvable but I just can´t see it.

Thanks in advance! 🙂

[v-frfei-msft]

Hi @pbiOP1 ,

Please change the relationship between table A and B to both as the picture below.

To use ALLSELETED instead of All here.

all = CALCULATE(SUM(TableA[IncomeObjectiveQ1]),ALLSELECTED(TableA[Office]))

[pbiOP1]

Thanks a lot for the answer, v-frfei-msft!

I just realized I did not specify how the tables are linked to eachother, which might be the reason why the solution you propose does not work.

Both tables are linked through a third, intermediate table, as the codes in TableA are not exactly the codes on TableB.

So TableA is as follows:

Office	IncomeObjectiveQ1	IncomeObjectiveQ2
A	10	10
B	10	10
C	10	10
D	10	10
E	10	10

TableB is as follows:

Contract	OfficeName	Income	Date
A1	AA	5	Q12019
B1	BA	5	Q12019
A2	AA	5	Q12019
C1	CA	5	Q12019
D1	DA	5	Q12019
E1	EA	5	Q12018
A3	AB	5	Q12019

And TableC is as follows:

Office	OfficeName
A	AA
A	AB
B	BA
C	CA
D	DA
E	EA

And they are linked the following way:

Does this change the problem significantly?

My apologies for the previously incomplete post, and thanks for your help!

[Ashish_Mathur]

Hi,

Show the exact result you are expecting in a Tabular format.  Once the Table is ready, you can create any visual you want.

[v-frfei-msft]

Hi @pbiOP1 ,

Did you created the relationship between tables like this?

Then I created the measure as below.

all = CALCULATE(SUM(TableA[IncomeObjectiveQ1]),ALLSELECTED(TableA[Office]))

[pbiOP1]

Hello again, v-frfei-msft!

Thanks a lot for the help! The relationship I created is exactly the one you describe in your post and its attached images, and the measure works when I use in a standalone visual (i.e. it shows the whole amount under column "IncomeObjective Q1").

The measure, however, is still being affected by data in other series in a visual, so the series does not show any data under "IncomeObjectiveQ1" belonging to any Office that gets filtered out by not having Income during the current year.

This seems to happen to your measure as well, as the amount shown on the card should be "50" (10*5 offices), but is instead 40 (Office E´s data, which does not have income within 2019, gets filtered out, resulting in 50-10).

This is exactly what I want to do: being able to show a full series in a visual, which does not change when related series are filtered out.

Thanks again for all the help and support! 🙂