Forum Discussion

Cscullio's avatar
Cscullio
Frequent Visitor
9 years ago

AllSelected In A Summarize Table

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(AllSelect...
Need Help
TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

as far as I understand your question I would create a measure like so

 

Median Monthly Sales = 
CALCULATE(
  Median('AllSales'[Units]),
  ALL('AllSales'[Month)
)

 

 

  • Cscullio's avatar
    Cscullio
    Frequent Visitor
    9 years ago

    Thanks Tom, but that isn't giving me what i need. 

    If we use the example below

    Month         SalesPerson            Units

    Jan              ID0001                   1000

    Jan              ID0002                   2000

    Jan              ID0003                   3000

    Jan              ID0004                   4000

    Jan              ID0005                   5000

     

    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 & ID0003 only.

    Is that possible to do?

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      Hey,

       

      it's possible :-)

       

      my table is called 'sampleALLSELECTED'

       

      Median Units = 
CALCULATE(
	MEDIAN('sampleALLSELECTED'[Units]),
	ALLSELECTED(sampleALLSELECTED[Salesperson])
)

      And you will get this

       

      Hope this will help

       

      • Cscullio's avatar
        Cscullio
        Frequent Visitor
        9 years ago

        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.  Is there a way to create a summarize table that updates according to selections?   Using AllSelected doesn't seem to have any effect.