I have been struggling for a few days trying to get the correct Average to show up in my Pivot table's subtotal.
I was unable to make it work with Calculated Fields but by creating a Calculated Column and doing my first calculation inside of it, I was then able to calculate my average field across a many to many relationship by using the average formula inside my first Calculate of the M2M Calculated Field.
FacingsCC:=SUMX(FILTER(Facings,AND(Facings[Div-Str]=SalesData[Div-Str],Facings[UPC]=SalesData[UPC])),Facings[Facings])
This first formula brought the denominator into the Calculated column of the Table I wanted to operate in.
UpFCC:=SalesData[SCANNED_MOVEMENT_CUR]/SalesData[FacingsCC]
This second formula conducted a simple division into another Calculated Column to see how many sales of each product in each store there were per facing of said product in said store.
AvgX:=CALCULATE(AVERAGE(SalesData[UpFCC]),FILTER(SKUsM6,CALCULATE(COUNTROWS(Facings))>0),FILTER(StoresM6,CALCULATE(COUNTROWS(Facings))>0))
Finally, this formula as a Calculated Field obtained the Average of the units sold per facing as per the filter contexts in the Pivot Table... i.e. it returns the proper average for both individual products and multiple products (store level).
Don't know if this will help anyone but I did find several people suggesting it was not possible. My assumptions are that it is not possible without doing some ETL (basically, a fancy name for manually manipulating the data in your model).
I am 1 week new to PivotTables, go easy on me.
Paul.
I was unable to make it work with Calculated Fields but by creating a Calculated Column and doing my first calculation inside of it, I was then able to calculate my average field across a many to many relationship by using the average formula inside my first Calculate of the M2M Calculated Field.
FacingsCC:=SUMX(FILTER(Facings,AND(Facings[Div-Str]=SalesData[Div-Str],Facings[UPC]=SalesData[UPC])),Facings[Facings])
This first formula brought the denominator into the Calculated column of the Table I wanted to operate in.
UpFCC:=SalesData[SCANNED_MOVEMENT_CUR]/SalesData[FacingsCC]
This second formula conducted a simple division into another Calculated Column to see how many sales of each product in each store there were per facing of said product in said store.
AvgX:=CALCULATE(AVERAGE(SalesData[UpFCC]),FILTER(SKUsM6,CALCULATE(COUNTROWS(Facings))>0),FILTER(StoresM6,CALCULATE(COUNTROWS(Facings))>0))
Finally, this formula as a Calculated Field obtained the Average of the units sold per facing as per the filter contexts in the Pivot Table... i.e. it returns the proper average for both individual products and multiple products (store level).
Don't know if this will help anyone but I did find several people suggesting it was not possible. My assumptions are that it is not possible without doing some ETL (basically, a fancy name for manually manipulating the data in your model).
I am 1 week new to PivotTables, go easy on me.
Paul.
