# Excel 2013 PowerPivot create category

#### maxwell888

##### New Member
I have a dataset which contains two fields and data similar to below.

I can create a powerpivot table as per below
 a 75 b 151

<tbody>
</tbody>

How can I create an extra category? - type c which is calculated from the other two - i.e. c = 2*a + 2*b

Note that in PowerPivot table tools -> analyse -> Fields, Items and sets, - Calculated field and calculated item are greyed out.
 a 75 b 151 c 452

<tbody>
</tbody>

Dataset
 Type Count a 43 a 32 b 76 b 54 b 21

<tbody>
</tbody>

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### MarcelBeug

##### Well-known Member
If you select a row label first, then you can add a calculated item c as 2 * a + 2 * b.

#### maxwell888

##### New Member
If you select a row label first, then you can add a calculated item c as 2 * a + 2 * b.

I'm sorry, I don't understand your answer. Do I select a row label in the pivot table? then how would I add a calculated item?

Thanks

#### MarcelBeug

##### Well-known Member
Yes and next, in Excel 2016: select Pivot table context menu - Analyze - Fields, Items & Sets - Calculated Item.
Name = c
Formula = 2 * a + 2 * b

By the way: this is rather an Excel question than a Power BI question.

#### maxwell888

##### New Member
Yes and next, in Excel 2016: select Pivot table context menu - Analyze - Fields, Items & Sets - Calculated Item.
Name = c
Formula = 2 * a + 2 * b

By the way: this is rather an Excel question than a Power BI question.

Yes, my post was moved from the excel forum to the powerbi forum
The calculated item is greyed out for me - I believe because I am using the data model to store my data. It seems like that is my real problem.

#### MarcelBeug

##### Well-known Member
Oops, I see.
In that case my suggestion would be to add a calculated column to your table in the datamodel and measures to sum the values.
the formula for the calculated column is: =if([Type]="a",2*[Count],if([Type]="b",2*[Count]))

If the calculated column name is "c-values", then, for the measure: c:=SUM(Table1[c-values])
And another measure for the sum of all counts: SumCount:=Sum(Table1[Count])

Now you can use these measure in the values part of your pivot table, which will put the results in columns.
I don't think it would be possible to have your c-values in an additional row.

Please note I'm not at all a DAX-expert, but as far as I know, this would be a proper way to do this.

Replies
2
Views
513
Replies
0
Views
419
Replies
6
Views
2K
Replies
0
Views
221
Replies
0
Views
398

1,190,743
Messages
5,982,699
Members
439,790
Latest member
jonaust

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back