Forum Discussion

Enzilmenz's avatar
Enzilmenz
Frequent Visitor
4 years ago
Solved

Median weighted

Hi all,   i need to calculate the median price of a material taking into consideration the impact of quantity purchased for such material. So i have this table   Material Price Quantity j...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Enzilmenz 

     

    I'm sorry I didn't understand your needs at first, now I do.

    You need to customize the function in power query to expand the corresponding number of rows, and then calculate the median value in desktop.

    Like this:

    let
    .......
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Quantity", each List.Transform(List.Numbers(1,_),(x)=>x/x)}}),
    #"Expanded Quantity" = Table.ExpandListColumn(Custom1, "Quantity")
in
    #"Expanded Quantity"

    If you don't want to destroy the source table, you can duplicate a table and operate it.

    Then you can directly use this code to calculate the median value.

    Below is my sample.

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Enzilmenz , I tried a new measure and I am getting value 3 using MEDIAN  and MEDIANX both

 

 

 

Enzilmenz's avatar
Enzilmenz
Frequent Visitor
4 years ago

hi thanks for replying.

I was talking about the median of the price, which is 5,5 but if considering the quantity purchased it should be 1.
so now i have this

how can i get the median to be 1?

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Enzilmenz 

    The problem is that I don't understand your logic, Why need 5.5 change to 1? You mentioned that the first row has a quantity of 5, so what? Can the 1 in the second row be ignored? 

    If you want the median divided by the total, you can try:

    measure =
DIVIDE ( MEDIANX ( table, [Price] ), COUNTROWS ( table ) )

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

     

    • Enzilmenz's avatar
      Enzilmenz
      Frequent Visitor
      4 years ago

      Hi,

       

      apologies for not explaining it correctly.

      The quantity is essential to calculate the median, in fact if we consider the quantity involved the series of price values for calculating the median would be (1,1,1,1,1,10) (five times the 1$ price, one time the 10$ price). Therefore the real median is 1, not 5,5 which is the results of considering only the series (1,10).
      thanks

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Enzilmenz 

         

        I'm sorry I didn't understand your needs at first, now I do.

        You need to customize the function in power query to expand the corresponding number of rows, and then calculate the median value in desktop.

        Like this:

        let
    .......
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Quantity", each List.Transform(List.Numbers(1,_),(x)=>x/x)}}),
    #"Expanded Quantity" = Table.ExpandListColumn(Custom1, "Quantity")
in
    #"Expanded Quantity"

        If you don't want to destroy the source table, you can duplicate a table and operate it.

        Then you can directly use this code to calculate the median value.

        Below is my sample.

        Did I answer your question? Please mark my reply as solution. Thank you very much.
        If not, please feel free to ask me.

        Best Regards,
        Community Support Team _ Janey