Values function works with separate table but not within same function

[theaddies]

I have a table within Power BI that I created using the formula below.

top_10_country_list_alternative = 
    topn(
    10,
    values(capacity_production_by_plant[Country]),
    calculate(sum(capacity_production_by_plant[production]),
    year(capacity_production_by_plant[year]) = 2023)
    )

When I use this table in the formula below everything works fine.

filtered production test = 
sumx(
    filter(
        capacity_production_by_plant,
        capacity_production_by_plant[Country] in
        values(top_10_country_list_alternative[Country])
        ),
        capacity_production_by_plant[production]
)

However, I am unable to integrate to the two formulas together so that I do not need to create the additional table.  The code below does not work.  I get an error 'The values function expects a column reference expression or a table reference expression for argument '1''

filtered production test = 
var country_list =
topn(
    10,
    values(capacity_production_by_plant[Country]),
    calculate(sum(capacity_production_by_plant[production]),
    year(capacity_production_by_plant[year]) = 2023)
    )
    return
sumx(
    filter(
        capacity_production_by_plant,
        capacity_production_by_plant[Country] in
        values(country_list)
        ),
        capacity_production_by_plant[production]
)

How do I go about integrating these two functions so I do not need to create a separate table?

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but please try something like below.

filtered production test =
VAR country_list =
    SUMMARIZE (
        TOPN (
            10,
            ALL ( capacity_production_by_plant[Country] ),
            CALCULATE (
                SUM ( capacity_production_by_plant[production] ),
                YEAR ( capacity_production_by_plant[year] ) = 2023
            )
        ),
        capacity_production_by_plant[Country]
    )
RETURN
    SUMX (
        FILTER (
            capacity_production_by_plant,
            capacity_production_by_plant[Country] IN country_list
        ),
        capacity_production_by_plant[production]
    )

[theaddies]

I don't get an error from this but it returns the sum of all production for the specfic year ignoring the top_10_country_list.

[Jihwan_Kim]

Hi,

Thank you for your message.

Could you please share your sample pbix file's link?