Forum Discussion

AlwaysDeepak's avatar
AlwaysDeepak
New Member
2 years ago

Calculate Median

Hi Datafam , I have two columns time to spin and spins. For each time to spin , there are multiple spins . Now i need to calculate median for time to spin. can someone please help me with the appropriate DAX formula to get the result.

 

Attaching the sample data here.

 

Time to spinSpins
11
27
31
45
55
62
74
81
92

 

 

Need Help

3 Replies

  • mlsx4's avatar
    mlsx4
    Memorable Member
    2 years ago

    Hi AlwaysDeepak 

     

    You can do it in DAX:

    Median spins = MEDIAN(MyTable[Time to spin])

    Or in Power Query:

     

    = Table.Group(#"Headers promoted", {"Spins"}, {{"Median", each List.Median([Time to spin]), type number}})

     

    • AlwaysDeepak's avatar
      AlwaysDeepak
      New Member
      2 years ago

      we have aggregated our data to number of spins to reduce number of records. This method will again explode my dataset. I already have 45M records. Any formula in DAX would be really helpful. Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi AlwaysDeepak ,

         

        I understand that you have aggregated your data to the number of spins to reduce the number of records. You can use the following DAX formula to calculate the median for the “Time to spin” column without exploding your dataset:

        MEDIANX(
    ADDCOLUMNS(
        GENERATESERIES(1, MAX('Table'[Spins]), 1),
        "Time to spin", DIVIDE(MAX('Table'[Time to spin]), MAX('Table'[Spins]))
    ),
    [Time to spin]
)

        Replace 'Table' with the name of your table. This formula will return the median value for the “Time to spin” column without exploding your dataset.

        I hope this helps!

        Reference: Solved: Calculate median of aggregated data - Microsoft Fabric Community

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.