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.

       

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        Hey,

         

        you can achieve what you want by using Month the Measure in a table vis

         

        The second table shows all the data.

         

        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.

         

        For this reason, you have to use a measure, unfortunately, but currently I can't see why a measure is not "good enough"