Calculate average by category where the averages respond to slicers

[jruf]

Hi there,

I'm sure this is a basic question but I haven't been able to solve it. 

I have a table with three variables, "date", "country" and "price". I just want to produce a new table which calculates the average price across countries, but where the values update depending on a slicer for "date". 

E.g. see first table below for example input table and example of what the output should look like

Input table

date	country	price
1/1/21	USA	20
1/2/21	USA	30
1/3/21	USA	40
1/1/21	England	10
1/2/21	England	20
1/3/21	England	30

What I want the output table to look like (assuming all dates are selected in the date slicer)

country	price
USA	30
England	20

[v-zhangti · Accepted solution]

Hi, @jruf 

Unfortunately, a Calculated Table is evaluated upon refresh of the datamodel. Thus selecting a slicervalue doesn't return a new evaluated calculated table.

Table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[country],
    "price",
        CALCULATE (
            AVERAGE ( 'Table'[price] ),
            ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[country] )
        )
)

This is a fixed table.

You can try the following methods.

Measure:

Measure =
CALCULATE (
    AVERAGE ( 'Table'[price] ),
    ALLEXCEPT ( 'Table', 'Table'[country], 'Table'[date] )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jruf]

Hi Ashish Mathur,

Thanks a bunch for this.

I should have been more specific in my question, but I want the output table which averages price by country to be a table in the same vein as the "calendar" and "data" tables you created - a dataset which appears in the 'fields' pane which I can reference in additional calculations. Are you able to advise on how I can do this?

Thanks again

Jon    

[Ashish_Mathur]

You are welcome.  I prefer not to use calculated table formulas.  Someone else will help you.