Add column that can be use in Row pivot table

[FranckN]

Hello

I am simplify my example to help you to understand my question

So let say I have in my table 3 columns

• Date
• Hours
• Amount

Example

Date	Hour	Amount
23-May-21	19:47:08	50
24-May-21	18:13:44	30
27-May-21	9:04:58	20
30-May-21	11:14:21	50

instead of having in my Pivot

• Date in (rows)
• and sum of amount in (Values.)

I add a colum in power pivot called shift like this

=if(hour>16:00,Date & "A", Date & B)

So my data is like this

Date	Hour	Amount	Shift
23-May-21	19:47:08	50	5/23/2021B
24-May-21	18:13:44	30	5/24/2021B
27-May-21	9:04:58	20	5/27/2021A
30-May-21	11:14:21	50	5/30/2021A

I would like to use in my pivot table

• Shift in (rows)
• and sum of amount in (Values.)

But I get error that I can move calculated field there.

What I can do? to be able to do it. what is the correct way.

thanks

[AlexisOlson]

I'd expect this to work fine unless you accidentally defined a measure instead of a calculated column for Shift.

Can you share a screenshot of the error you're getting?

[FranckN · Accepted solution]

I found it

Just add a column and not Calculated column

thanks