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]

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?