AllSelected In A Summarize Table

Question

I have a table of Months, Salesperson and Sales.

I want to create a summary table of the median sales total for each month.

I am using the following formula

FilteredValues = Summarize(AllSelected(AllSales), AllSales[Month], "Median Monthly Sales", Median(AllSales[Units]))

But when I filter on a subset of the sales people the table remains static and gives the total for all sales people.

How do I create a summary table that calculates median based on the selections only?

cscullio · Answer

Thanks Tom, but that isn't giving me what i need.&nbsp;If we use the example belowMonth&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SalesPerson&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UnitsJan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ID0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1000Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ID0002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2000Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ID0003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ID0004 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4000Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ID0005 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5000&nbsp;I want to create a summary table that gives a median of 3000 when there is no filter on SalesPerson but gives a Median of 2000 if I am filtering on ID0001, ID0002 &amp; ID0003 only.Is that possible to do?

tommartens · Answer

Hey,&nbsp;you can achieve what you want by using Month the Measure in a table vis&nbsp;The second table shows all the data.&nbsp;You can't use a calculated table / virtual table, as a source for a visual, due to the fact that it is static (reflecting the moment (current selections) when you calculate the table.&nbsp;For this reason, you have to use a measure, unfortunately, but currently I can't see why a measure is not "good enough"

tommartens · Answer

Hey,&nbsp;as far as I understand your question I would create a measure like so&nbsp;Median Monthly Sales = 
CALCULATE(
  Median('AllSales'[Units]),
  ALL('AllSales'[Month)
)&nbsp;&nbsp;

tommartens · Answer

Hey,&nbsp;it's possible :-)&nbsp;my table is called 'sampleALLSELECTED'&nbsp;Median Units = 
CALCULATE(
	MEDIAN('sampleALLSELECTED'[Units]),
	ALLSELECTED(sampleALLSELECTED[Salesperson])
) And you will get this&nbsp;Hope this will help&nbsp;

cscullio · Answer

Hi Tom,Thanks but that is not quite what I am trying to achieve.I need a table which has one row per month and gives the mendian for each month based on the selections, so I am trying to do this as a Summarize table.&nbsp; Is there a way to create a summarize table that updates according to selections?&nbsp;&nbsp; Using AllSelected doesn't seem to have any effect.&nbsp;

Forum Discussion

AllSelected In A Summarize Table

5 Replies

Related content

Merge Summarize Table

Parameter table dimensions - summarize

SUMMARIZE Table

Parameter in summarize

Summarize average monthly table