Measure to Repeat a subtotal value in a Pivot Table column

P

peter789

Board Regular
Joined
Nov 20, 2016
Messages
130
I hope I can explain my data OK but here goes:
I have a list of dates when production samples were tested for various properties. During the period runs of product might belong to different categories each with a different specification. I would like to calculate the average value for a property for each different category grouping within the table filter context. If I remove the sample test dates from my table all is well but I need that level of detail for a control chart. Using ALLSELECTED I can calculate the average for all the dates I have selected using the table filters. This measure fills a column of data in the report. What I would like to do is refine this so the average is calculated on the group for each category but in a similar manner to above fills each row of the column against the relevant date and category.
I have been fighting this for 3 days so any hints or pointers would be welcome please.
(By the way the category group is in a related lookup table so I cannot use its column name from the working data table).
Peter
 
Sort by date Sort by votes
Sorry, I can't follow your problem at all. Can you write it out where you describe more mechanically ie what your columns are and mathematically what the calculation should be. A snap shot of the data wouldn't hurt either.
 
Upvote 0
Here's the data from the Pivot Table. Sorry for the format I can't work out how to add a screen shot.

[TABLE="width: 414"]
<tbody>[TR]
[TD][/TD]
[TD]Average Value[/TD]
[TD][/TD]
[TD]Average Selected values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample Date[/TD]
[TD]Flint[/TD]
[TD]Green[/TD]
[TD]Flint[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD="align: right"]11.79[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2017[/TD]
[TD="align: right"]11.87[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.20[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.21[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.30[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD="align: right"]12.02[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017[/TD]
[TD="align: right"]12.02[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/05/2017[/TD]
[TD="align: right"]11.96[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017[/TD]
[TD="align: right"]11.97[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD="align: right"]11.84[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/06/2017[/TD]
[TD="align: right"]11.96[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/06/2017[/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/06/2017[/TD]
[TD="align: right"]11.90[/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col></colgroup>[/TABLE]

The Average Value is the test result of the sample taken on the date shown and is the average of the values taken from the [Value] Column of the data table 'QryAnalysis' .
Average Value=AVERAGE(QryAnalysis[Value])
In this example there are two categories one called Flint one called Green (there are several other available). The Average Selected Value is calculated with the following measure:

=if(ISBLANK([Average Value]),BLANK(),CALCULATE(AVERAGEX(QryAnalysis,[Value]),ALLSELECTED(QryAnalysis)))

The Average Value Selected takes all the Average Values into the calculation.
What I would like it to do is work out the averages for each of the category groups independently.
So the Flint Average is 11.93 and the Green Average is 12.24.
This is the equivalent of removing the sample date field from the table like so:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Average Value[/TD]
[TD][/TD]
[TD]Average Selected values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flint[/TD]
[TD]Green[/TD]
[TD]Flint[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]11.93[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]12.00[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="2"></colgroup>[/TABLE]

But I can't do that as I need to plot the individual results on a chart.

Peter
 
Upvote 0
Do I understand that you want the same result in each row, but instead being 12.00,

In your ALLSELECTED(), try putting just the columns you want to remove the filter from. ie ALLSELECTED(QryAnalysis[Dates]).

Separate point, but would think you could replace your AVERAGEX with [Average Value], you aren't really doing any row by row calculations that I can see.
 
Upvote 0
Many thanks for helping me over that obstacle. AVERAGEX duly eliminated as well!
Results now as required. I can carry on building my control chart.

[TABLE="width: 525"]
<tbody>[TR]
[TD][/TD]
[TD]Average Value[/TD]
[TD][/TD]
[TD]Average Selected values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample Date[/TD]
[TD]Flint[/TD]
[TD]Green[/TD]
[TD]Flint[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD="align: right"]11.79[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2017[/TD]
[TD="align: right"]11.87[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.20[/TD]
[TD][/TD]
[TD="align: right"]12.24[/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.21[/TD]
[TD][/TD]
[TD="align: right"]12.24[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD][/TD]
[TD="align: right"]12.30[/TD]
[TD][/TD]
[TD="align: right"]12.24[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD="align: right"]12.02[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017[/TD]
[TD="align: right"]12.02[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/05/2017[/TD]
[TD="align: right"]11.96[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017[/TD]
[TD="align: right"]11.97[/TD]
[TD][/TD]
[TD="align: right"]11.94[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="3"></colgroup>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Power BI Measure to combine Headcount by Month, Year and Company selected by Slicer
Replies
0
Views
132
WxShady13
W
M
  • Question Question
Latest Weeks Volume Issue with DAX
Replies
2
Views
187
JEC
JEC
R
  • Question Question
Filter is not applied to a measure in the detail table
Replies
1
Views
200
sanalytics
S
I
  • Solved
DAX measure that filters a table to a single row and returns text from a specified column (Excel365)
Replies
3
Views
632
Insert_Key
I
S
  • Question Question
Power BI Measure Calculation
Replies
3
Views
188
Simonc64
S

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top