Forum Discussion

PowerBIzNess's avatar
PowerBIzNess
Frequent Visitor
9 years ago

Referencing a Measure in a Column

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

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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's avatar
      PowerBIzNess
      Frequent Visitor
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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