if I apply a formula to a calculated field in pivot table, is that DAX or excel function?

[INN]

Hi I have a pivot table (Rows = dept name, Values = Salary). I want to insert another inside Values box to show rounded salaries. So under pivot analyze, I click field-->Insert Calculated Field., the wrote this formula

= MROUND(Salary,1000)

Everything is right. I wonder what is this Mround() function? Is it excel function or it is DAX function. Can I use any function with calculated field? Thank you very much.

 

[Alex Blakenburg]

If you can add a calculated field, it is a standard pivot and the formula is a standard excel function.

Mround lets you round to the multiple of any number. If the number you are rounding to is a multiple of 10 which 1000 is, you would often use Round(Salary,-3) instead.

You can use most Excel functions but keep in mind that you can only reference data from the columns used in the pivot table and it will only apply to the cells in those columns that are on the same row.

 

[INN]

Thank you very much

 

[INN]

Thanks for your reply. I appreciate it. This article is saying that only SUM function can be used for calculated field:
"Sum is the only function available for a calculated field."
Is that right?

Article link

How to Create Excel Pivot Table Calculated Field Examples

Examples how to create calculated field in pivot table, and remove temporarily or permanently. Video and written step-by-step instructions

www.contextures.com

 

[Alex Blakenburg]

Interesting, I have not taken much notice of that before.
The below is a really bad example in that multiplying Qty x Price in a pivot is mathematically incorrect and a really bad idea but it illustrates the point.

In a standard Pivot Table (as opposed to using Power Pivot and DAX), when you create the calculated field, you only get an option of picking the fields.
You don't get an option of picking whether you are using the Sum of that field or the Average of the field or the Max or anything else of that field.
One might assume from that it will use the method applied to that field in the Pivot. This is not the case. The calculation will assume that you will be summing the fields used in the calculation.

So in the below the left table uses summarisation methods other than Sum but the result of the calculated field is the same as when the fields are using the Sum method of summarisation. You can test the calculation to prove that Sum is what is being used for the calculated field in both cases.