Forum Discussion

Derva's avatar
Derva
New Member
4 years ago
Solved

Create Measure for all columns/variables/questions within a table

Hi There,

 

I'm still very new to Power BI and I hope there is a solution to my question because it would become very inefficient if I need to do this on a per column/variable basis.

Within a matrix we always have multiple questions on the rows, we currently show the average score on a per year basis. Like so:

For both questions I needed to create a custom measure as we don't want to allow code 99 to be scored, 99 should become null:

Avg_cAlgemeen1 = CALCULATE(AVERAGE('Table Test v1_1'[cAlgemeen1]),'Table Test v1_1'[cAlgemeen1] <> 99)
Avg_Calgemeen2 = CALCULATE(AVERAGE('Table Test v1_1'[cAlgemeen2]),'Table Test v1_1'[cAlgemeen2] <> 99)

Is there a way that I can create one measure for all questions/variables/columns within that matrix/table with one line of code?

Thanks!
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Derva ,

    According to your description, to reproduce your problem, I create a sample.

    Put Category in Matrix Rows, 2021 and 2022 columns in Matrix Values.

    Now, you want to show the average of 2021 and 2022 seperately with one measure, I'm afraid you can't do that.

    Or you can change the data structure. In PowerQuery, select the 2021 and 2022 columns at the same time, then click Unpivot Columns under the Transform ribbon.

    Now, 2021 and 2022 are in one column and the values are in another column.

    Then, create a measure.

    Average =
AVERAGEX ( FILTER ( 'Table', 'Table'[Value] <> 99 ), 'Table'[Value] )

    Put Year column in the Matrix Columns and the measure in the Matrix Values, get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Derva ,

    According to your description, to reproduce your problem, I create a sample.

    Put Category in Matrix Rows, 2021 and 2022 columns in Matrix Values.

    Now, you want to show the average of 2021 and 2022 seperately with one measure, I'm afraid you can't do that.

    Or you can change the data structure. In PowerQuery, select the 2021 and 2022 columns at the same time, then click Unpivot Columns under the Transform ribbon.

    Now, 2021 and 2022 are in one column and the values are in another column.

    Then, create a measure.

    Average =
AVERAGEX ( FILTER ( 'Table', 'Table'[Value] <> 99 ), 'Table'[Value] )

    Put Year column in the Matrix Columns and the measure in the Matrix Values, get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.