Custom Formulas for Calculations in Pivot Table

Hi Gaurav,

As usual there are few ways to do that, depends which version of Excel do you have and how you are familiar with data model aggregations.

1) That's without using data model

- Add to your table helper column to calculate distinct number like

=IF(SUMPRODUCT(($F$5:$F5=F5)*($G$5:$G5=G5))>1,0,1)

- Create Pivot Table WITHOUT adding it to data model

- From Analysis menu add calculated field to average based on distinct count

=SUM(Wise Order Amt)/Helper

2) If your version of Excel is with Power Pivot you may

- from Power Pivot menu add your source table to data model

- within Power Pivot add measure as

Avr Distinct:=SUM(SourceTable[Wise Order Amt]/DISTINCTCOUNT(SourceTable[CP ID])

- from Power Pivot add to excel PivotTable with updated bu above measure data model

3) If you don't have Power Pivot.

You don't have direct access to data model from within Excel (I still can't understand why Power Query is free for everyone with any Excel, and Power Pivot is only for "Pro" versions. But that's another story).

However, there is the workaround.

- Based on your source table add any tiny Pivot Table with ADD TO DATA MODEL option. At any place and with any one field - after that you don't need it any more. The only purpose of this pivot table is to add your source table to data model

- Find in menu Data->Existing connections, select here your source table and add it to Excel as new table

- you shall receive exact copy of your source table

- to check, right click within the table as on screenshot and find edit DAX

- now a bit of DAX coding is required. That's not a rocket science, based on sample below and with some help from Google i believe you may modify the sample for your needs

EVALUATE
CALCULATETABLE(
   SUMMARIZE(
      SourceTable,
      SourceTable[Category],
      "Dst CP ID", DISTINCTCOUNT(SourceTable[CP ID]),
      "Dst Avr Amt", DIVIDE(SUM(SourceTable[Wise Order Amt]),DISTINCTCOUNT(SourceTable[CP ID]))
   )
)

Here

EVALUATE returns expression after it to Excel as the table

SUMMARIZE is the builder of the table,

First parameter is the name of your source table as it is in yout Excel sheet  (in my case i simply called it SourceTable),

After that separated by comma fields you'd like to add to your new table. If you repeat the name of your source table thats simply TableName[Field Name] (e.g. SourceTable[CP ID]).

If that will be calculated field you first shall add string with such field name and after that the expression with its calculation.

I guess names of aggregation functions in the sample are self-explained.

- Copy you DAX code and add to new table through Edit DAX right click menu

- now you have the table with desired fields (it's refrshed by right click menu or by Data->Refresh All). You may build your chart directly on this table, or based on this new table create one more PivotTable. (to add slicers, etc)

File with sample is attached