Forum Discussion
Grouping data in the table view
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?
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?
7 Replies
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?It worked! Thank you so much!!!!
Great. If it's working, please mark my post as the solution.
Thank you
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]) )I am getting the same error
Could you provide a sample of your data and the expected output so I can take a look?
