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 appropr...
Need Help
mlsx4's avatar
mlsx4
Icon for Memorable Member rankMemorable 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.