Cannot add calculated columns in a pivot table

[GON76]

I'm trying to add two caluclated columns into a pivot table visual.  I created the columns in the Table view, and when I drag them to the column field, they aren't appearing.  Am I doing something wrong?

[v-agajavelly · Accepted solution]

Hi @GON76 ,

Thanks for clarifying now it makes total sense what you’re trying to do. The reason those fields (Compliance Status and Learning Path Completion) “disappear” when you drop them into the Matrix is because the Matrix visual doesn’t work like Excel. Instead of just showing columns side-by-side, it turns them into a hierarchy under Columns, which is why you only see drill-down options instead of flat columns.

Since you just want those calculated fields to show up as normal columns, the easiest fix is.

• Use a Table visual instead of a Matrix.
• In the Table, every field you drag in (Username, Compliance Status, Learning Path Completion, etc.) will show up as a separate column exactly how you expect.

So,

• Table = flat columns (what you want here).
• Matrix = grouped/expandable view (good for summarizing, but not great for your use case).

If you switch the visual to Table, you’ll see those calculated columns right away with no drilling or extra steps.

You’re not doing anything wrong this is just how the Matrix is designed. For your scenario, the Table visual is the right one.

Regards,
Akhil.

[GON76]

Thank you Akhil.  I figured this would be the issue.  Thanks for your help!

[v-agajavelly]

Hi @GON76 ,

It’s not really a problem with your calculated columns or Power BI being buggy, it’s actually the matrix visual doing what it’s designed to do, but in a slightly confusing way.

Here’s the deal, when you use a matrix and add multiple fields into the “Columns” section, Power BI treats those as a hierarchy. And when that happens, you’re essentially looking at just the first level of that hierarchy unless you explicitly expand it. So those calculated columns you’re adding (like Compliance Status or Learning Path Completion) might be sitting under the hierarchy, but not actually visible until you drill down.

Here’s what fixed it for me.

1. Click on the matrix visual so you see the matrix toolbar on top.
2. Where it says “Drill on: Rows”, change that to “Columns”.
3. Then, click the little double-down arrow that’s the “Expand all down one level in the hierarchy” button.
• This should open up the rest of the fields in your column hierarchy.
4. Still not what you want? You can flatten it completely:
• Go to the Fields pane and just drag the columns one by one into the “Columns” bucket (instead of nested).
• That removes the hierarchy structure altogether.

If the little +/- icons on the headers are cluttering the look, you can hide them:

• Go to the Format pane → Row headers → Turn off “+/- icons”.

Once I did all that, everything showed up exactly how I wanted no need to redo calculated columns or rebuild visuals.

Regards,
Akhil.

[GON76]

Hi Akhil,

Thanks for responding.  I was able to remove the +/- icons.  I can only see the Drill Down option when I move the Compliance Status and Learning Path Completion fields to the columns, but then they disappear from the matrix table visual.  The table is expanded down all the way.  Sorry, I'm a novice at Power BI.  Thanks for your help!

[GON76]

Sure thing, the two columns that I'm trying to add are Compliance Status and Learning Path Completion. Let me know if there's anything else you'd like to see. Thanks in advance!

[danextian]

Hi @GON76 

You haven't clicked drill down down or expand columns so the view is stuck to the first column in the hierarchy. Change Drill on to Columns and click Expand Hierarchy.

[GON76]

Hi Francesco,

Is there a way to remove the hierarchy so that all of the data is expanded into separate columns without the expand/collapse button?  Would this allow me to add those two calculated columns to the matrix table?

Thanks!

[danextian]

Hi @GON76 

You can expand the hierarchy all the way down and hide the header icons related to the hierarchy.

[FBergamaschi]

I cannot understand from this image, sorry. It might be a problem of just drilling down to the right level

Can you create a new visual, add just a couple of columns into rows, a couple into columns and check if everything works?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[GON76]

I created a visual with just the username as a column, the names as rows, and exitdate as values.  I'm still unable to add the columns.  Is this a limit with pivot tables when handling lots of data?

[FBergamaschi]

Not that I know, can you share the file in someway (cloud) so I double check what is going on?

[GON76]

Unfortunately, I can't share it.  I can provide screenshot of anything you want to see. I really appreciate the help!

[FBergamaschi]

Can you please show an image?