Referencing a Measure in a Column

[PowerBIzNess]

Hi there,

I'm trying to reference a measure (Monthly Average Sales) in a column, but it's being returned as a column. Any suggestions? I'm trying to use this measure to forecast future months.

Formula looks like this:

= Table.FromRows({{"Dept A", "Team 1", MeasureTable[AverageSales]}}, {"Department", "Team", "Average Sales FCST"})

Any help would be appreciated!

Thanks,

M

[v-huizhn-msft]

Hi @PowerBIzNess,

Have you resolved your problem? You'd better describe you issue clearly. Post some sample data, and measure, what result you want to achieve. So that we can post the solution which is close to your requirement.

Best Regards,
Angelia

[Greg_Deckler]

What do you mean being returned as a column? You have to be a bit careful with measures in columns, since they are context aware, you can get funky results and have to structure your measure carefully.

[PowerBIzNess]

Thanks for the reply. 

With the expression above, I get an error in my calculated column:

"Expression.Error: The column 'Average Sales' of the table wasn't found."

My measure 'Average Sales' exists in a separate measure table, defined by the following calc:

Average Sales = CALCULATE(SUM(Reporting[Sales]), Lookup_table[Departments]="Dept A")/DISTINCTCOUNT('Reporting'[Reporting Period])

Basically what I have is a measure calculating the average sales to date for a particular department, and I am trying to pull that measure into a separate table to then use as a forecast for future periods for that department. 

The Reporting data source is updated monthly with actuals.

Thanks in advance!

M

[v-huizhn-msft]

Hi @PowerBIzNess,

In Power Query Edit, we only can operate the fields in your resource data table. Then measure 'Average Sales' is the filed created by yourself, so you return the error message.

For your requirement, you'd better create another measure similar with 'Average Sales' , just replace the "Dept A" to other departments.

Thanks,
Angelia