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

 

MaterialPriceQuantity
j115
j1101

 

If i use the standard MEDIAN formula, powerbi gives me 5,5 as result, however the real outcome should be 1, based on the fact that line 1 has 5 quantity.

How can i get the correct result with dax?

alternatively i know i could add a new row for every quantity i have but my database would grow into millions of rows and it would be impossible to load.

 

Thanks a lot

Enzo

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

5 Replies

    • 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