User MEDIAN of rolling 7 day total

[dstarrynight]

I'm trying to calculate the median of the all user's total over the last 7 days. The data is structured on a daily basis and each user can have mutliple amounts in a given day. I would like to SUM each user's total over a rolling 7 day period and then find the median amount of the total. I'm using MEDIANX and SUMMARIZE which will calculate the MEDIAN correctly if used in a card, but when I try to use the measure in a table, it's only calculating the median for a single day. Below are my formulas. 

User Total = SUM(Users[Amount])

User Total L7d =
     CALCULATE(
     [User Total],
     DATESINPERIOD ( Users[Date], LASTDATE(Users[Date]), -7, DAY )
)

Median Total L7d =
    MEDIANX (
          SUMMARIZE (
                    Users,
                    Users[UserID],
                    "Median Total L7d", Users[Users Total L7d]
          ),
          [Median Total L7d]
)

The formula "User Total L7d" is calculating the rolling sum correctly in the table, but when I add the "Median Total L7d" to the table (by date) it only calculates the median for that day. Sorry I can't post any screenshots, the data is confidential. Please let me know if anything is confusing and I can provide more detail. 

[Ashish_Mathur]

Hi,

Try this

Median Total L7d =MEDIANX(SUMMARIZE(VALUES(Users[UserID]),[UserID],"Median Total L7d",[Users Total L7d]),[Median Total L7d])

Hope this helps.

[dstarrynight]

Thanks Ashish, the formula worked, but I discovered my issue has another problem. The Median formula you provided only calculates median user's 7 day total if they were active on that day. I failed to mention this in my original post. I need to calculate the median of the user's 7 total who have been active in any of those 7 days. I hope that makes sense, I included a screenshot in my other reply showing this. I appreciate you taking the time to help.

-d

[v-piga-msft]

Hi @dstarrynight,

From my test, you could modify your measure like below, then you will get the median by date.

Median Total L7d =
MEDIANX (
    FILTER (
        SUMMARIZE (
            ALL ( Users ),
            'Users'[UserID],
            'Users'[Date],
            "Median Total L7d", Users[User Total L7d]
        ),
        'Users'[Date] = MAX ( 'Users'[Date] )
    ),
    [Median Total L7d]
)

The picture of result is below.

Hope it can help you! 

If you need additional help please share some data sample and expected output.

Best Regards,

Cherry

[dstarrynight]

Thank you for taking the time to respond. Your formula works, but the reason I'm having reconciling is happening with the Users[User Total L7d] calculation. I neglected to mention this in the original post, but it's not guaranteed to be the same users on a daily basis. So, the median calculation is only calculating the median of the 7 day total of the users on that day. Where as, I would want the median of all user activity in the last 7 days regardless if they were active on that day. 

Is there somewhere I can share my dummy data file with you? I really appreciate the help.

-d

[Ashish_Mathur]

Hi,

Upload your file to Google Drive and share the download link here.  Please shoe the exected result very clearly there.

[dstarrynight]

PBIX file here: https://drive.google.com/file/d/1b4C4O46eWGmh3ESQrG1WsySw3icUZa1E/view?usp=sharing

There's a text box with the expected results by date. 

[Ashish_Mathur]

Hi,

I am confused.  On 3/1/2017, the Median as per my formula is 164.50.  As per you, the answer should be 143.  So here are my question:

1. How did you arrive at the 143 number?
2. We do not have data for user ID 1 and 10 on 3/1/2017 so shouldn't the median be calculated only for balance 8 employees only?

Please clarify.

[dstarrynight]

Hi Ashish,

1. 143 is the median for the range 2/26/2017 - 3/1/2017 (should be 7 days, but the data only goes back to 2/26). 

2. This was the issue I missed on the OP. I would like to calculate the median of the user's total over all 7 days even if they did not have activity on the specific day. There was no data for users 1 and 10 on 3/1, but user 1 had data on 2/26 and 2/27 and user 10 had data on 2/27. 

This is a screenshot showing how I would like to calculate the median and your formula works if the data is formatted this way, but I also need to add the daily time element which excludes users if they were not active on that day.

 

Thank you for looking into this for me!

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Median Total L7d (Ashish) = MEDIANX(CALCULATETABLE(SUMMARIZE(VALUES(Users[UserID]),[UserID],"Median Total L7d",[User Total L7d]),DATESBETWEEN(Users[Date],MAX(Users[Date])-7,MAX(Users[Date]))),[Median Total L7d])

Hope this helps.

[dstarrynight]

You are amazing! I can't thank you enough. . I had to change the summarize [User Total L7d] to just [User Total], but thank you so much for helping me with this!

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.