Using constant Total from Linked table

[Anonymous]

Good morning,

I'm stuck in a seemingly easy problem, illustrated below:

I have two linked tables:

I'm trying to calculate the % of TV and Cinema in a bar chart with a slicer for choosing Country. If I use Count as % of GT in the second table only, it gives me a wrong number because it takes into account all rows, where Respondents are duplicated (as it is unpivoted).

I then tried the following formula: %Value=COUNTA('Tab2'[Value]) / CALCULATE(Sum('Tab1'[Count]),ALL('Tab1') but this returns the correct results for the total sample, but wrong results when I start slicing by Country (because the Sum of Count remains constant so it always divides by the total sample.

To illustrate, this is what I'm trying to achieve:

 I tried a lot of variations of the formula above, but never got it work for both Total sample and by country. What am I missing?

Any help much appreciated!

George

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please modify your measure as below:

%Value =
COUNTA ( 'Tab2'[Value] )
    / CALCULATE ( SUM ( 'Tab1'[Count] ), ALLEXCEPT ( 'Tab1', Tab1[Country] ) )

Best regards,
Yuliana Gu

[Anonymous]

Thank you very much Yuliana!

Your solution did the trick (+ getting rid of a two-way relationship between my tabs!)

George.