Filter on a table where Total Cost for a group is grater than the average cost of the same group

[abalgir]

Hello,

I have 2 tables :

A table that gives the average cost per country

another one that gives the total cost per country

I would like to create a visual that lists only the countries where Total Cost > Average Cost i.e:

FRANCE and USA

What is the best way to do that for a very large table?

Thank you 

Gerard

[v-lid-msft]

Hi @abalgir ,

We can try to create a measure, then put it into visual filter and set condition as "is 1" to meet your requirement:

Visual Control =
IF ( [TOTAL COST] > [AVG COST], 1, -1 )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive For Business and share the link here.

Best regards,

[amitchandak · Accepted solution]

@abalgir ,

Try something like this - sumx(filter(summarize(Country[Country], "_Avg",[Avg Cost],"_sum",[Total Cost]), [_sum]>[_avg]),[_sum])

You can replace [_sum] with [_avg]

[danextian]

hI @abalgir ,

Are those tables two separate tables in your data model or are they from the same table but shown in separate visuals?

The formula below works if the data is coming from the same table.

Country measure =
SUMX (
    VALUES ( 'Table'[Country] ),
    VAR __TOTAL =
        CALCULATE (
            SUM ( 'Table'[Total Cost] ),
            ALLEXCEPT ( 'Table', 'Table'[Country] )
        )
    VAR __AVERAGE =
        CALCULATE (
            AVERAGE ( 'Table'[Total Cost] ),
            ALLEXCEPT ( 'Table', 'Table'[Country] )
        )
    RETURN
        IF ( __TOTAL > __AVERAGE, __TOTAL )
)

[abalgir]

Hi,

Thank you for the response, these are 2 separate tables

[abalgir]

I tried on the same table and both methods work.

Thank you

[abalgir]

I tested on the same table and both methods work.

Thanks