Grouping data in the table view

[daniel_krantz_4]

I am new to Power Bi and am having issues figuring this out. I want to have a column that gives average sales by provider by month. Each row is a day, I have a "month year" column", and then there are 8 different providers for each of these days. I know how to get these aggregations in R, but would like to do it right in power bi with dax.  

What is the reason this code gets an error? 

Group sales by Month by Provider = SUMMARIZECOLUMNS('sales'[PROVIDER], 'sales'[Month Year], "avg monthly sales", AVERAGE('sales'[sales]) )

Error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

I get similar errors when I try figuring it out with the group by function or averageX. What am I doing wrong and is there a simpler way to get a column that gives this?

[_AAndrade · Accepted solution]

I'm using this table to simulate tour data set:

I added a new column wich computes what I understoud from your explanation. The measure is this:

Column = 
VAR _CurMonth = T_Sales[Month]
VAR _Provider = T_Sales[Provider]
VAR _Result = 
    CALCULATE(
        AVERAGE(T_Sales[Sales]),
        FILTER(
            ALL(T_Sales),
            T_Sales[Month] = _CurMonth && T_Sales[Provider] = _Provider
        )
    )
            
RETURN
    _Result

And the final output is this:

For example, for provider A in January the Average is 150 ((100 + 200 / 2)), so this number repeats for each day of January for that provider. In February the Average will be diferent.
This is what you're looking for?

[daniel_krantz_4]

It worked! Thank you so much!!!!

[_AAndrade]

Great. If it's working, please mark my post as the solution.

Thank you

[_AAndrade]

Hi @daniel_krantz_4,

Did you try SUMMARIZE instead of SUMMARIZECOLUMNS?
I would try something like this:

ADDCOLUMNS(
    SUMMARIZE(
        'sales'[PROVIDER],
        'sales'[Month Year]
        ),
    "avg monthly sales", AVERAGE('sales'[sales]) 
)

[daniel_krantz_4]

I am getting the same error

[_AAndrade]

Could you provide a sample of your data and the expected output so I can take a look?

[daniel_krantz_4]

So the idea is that for each row in July for each provider that extra empty column would show the average sales in July on each row (same number would repeat a couple times, then a new provider so a new number would repeat a couple times, then a new month so it all iterates again.