Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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?
Proud to be a Super User!
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?
Proud to be a Super User!
It worked! Thank you so much!!!!
Great. If it's working, please mark my post as the solution.
Thank you
Proud to be a Super User!
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])
)
Proud to be a Super User!
I am getting the same error
Could you provide a sample of your data and the expected output so I can take a look?
Proud to be a Super User!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |