cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Using constant Total from Linked table

Good morning,

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

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

1 ACCEPTED SOLUTION
Employee

Hi @Anonymous,

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

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Employee

Hi @Anonymous,

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

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you very much Yuliana!

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

George.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors