Cannot move calculated column in matrix

[Anonymous]

Hi,

I have a matrix displaying production data as is shown in the uploaded screenshot. 

The outstanding quantity column is a calculated field and ideally I want it placed after the status total column but I cannot see a way of doing it. The outstanding quantity is row data and I read that what I need to do is change the pivot from row to column but when I go into power query editor to pivot the column, the column is not there. Is that because it is calculated and if so how would I get round this? When I tried to  add a custom column and pasted in the code that I used for the calculated column it errored. This is the code i used

Outstanding Quantity = Query1[JobQty] - CALCULATE (

    COUNT ( 'Query1'[Status]),

    ALLEXCEPT ( 'Query1', 'Query1'[JobNumber] ))

Can someone point me in the right direction?

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

It couldn't be achieved that move the column place after [total] column in power bi for now. It could only be adjusted in "attribute" area.

and for second question, The calculate column couldn't appear in Edit Queries. Because Calculate column is based on DAX language and data model, but column in Edit Queries is based on M language and data soure.

https://radacad.com/m-or-dax-that-is-the-question

If you still want to pivot it, you could use same logic create a custom column in edit queries then pivot.

Best Regards,

Lin

[ibarrau]

Hi. I think that you will complicate your model for this. I usually say no to this kind of things, because you allready have what you need there.

Anyway, the only way to have that value at the end, is if you remove all data columns from the matrix and add measures for any columns you want. E.g. Measure for F values, R values... for Status total and finally for Outstanding Quantity.

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