Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Using constant Total from Linked table

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

  • Hi Anonymous,

     

    Please modify your measure as below:

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

    Best regards,
    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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's avatar
      Anonymous
      Not applicable

      Thank you very much Yuliana!

       

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

       

      George.