How do I calculate the median of the output of a measure?

[scorbin-j]

I am trying to find the median of the weekly issued amount for materials. My first issue was needing to fill in the gaps of weeks without any issuing data and create new rows with 0 when there wasn't a row previously. I believe I have done that with this measure: 

QuantityMissingDates = 
    SUMX(
        SUMMARIZE(
            AverageDailyUsage,
            dimTime[ISO_WEEK],
            BRIDGEMM[Materialfull],
            AverageDailyUsage[WeekSUM]
        ),
        [WeekSUM]
    ) + 0

Just looking at one material that gets me this table visual:

Week	Material ID	Qty
2024 24	1017983	224
2024 16	1017983	192
2024 08	1017983	128
2024 18	1017983	128
2024 05	1017983	96
2024 19	1017983	96
2024 26	1017983	96
2024 13	1017983	64
2024 15	1017983	64
2024 04	1017983	32
2024 20	1017983	32
2024 03	1017983	0
2024 06	1017983	0
2024 07	1017983	0
2024 09	1017983	0
2024 10	1017983	0
2024 11	1017983	0
2024 12	1017983	0
2024 14	1017983	0
2024 17	1017983	0
2024 21	1017983	0
2024 22	1017983	0
2024 23	1017983	0
2024 25	1017983	0
2024 27	1017983	0
2024 28	1017983	0
2024 29	1017983	0

So those 0 quantities are all "fake" numbers since there wasn't any original data there for those particular weeks. My question is how do I find the median of that new measure that includes the 0s and is dynamic to a date filter and material id filter? 

[Anonymous · Accepted solution]

Hi @scorbin-j ,

According to your statement, I think your data model should look like as below.

I suggest you to try code as below to create a measure.

median = 
VAR _GENERATE = ADDCOLUMNS(GENERATE(VALUES(DimTime[ISO_WEEK]),VALUES(BRIDGEMM[Materialfull])),"Qty",[QuantityMissingDates])
RETURN
MEDIANX(FILTER(_GENERATE,[Materialfull] = MAX(BRIDGEMM[Materialfull])),[Qty])

Result is as below.

Best Regards,
Rico Zhou

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

[scorbin-j]

This seems to work, but I also found this method as well:

Median = 
var _table = 
ADDCOLUMNS((Summarize(allselected(dimTime),dimTime[ISO_WEEK])), "_qtywithzeros",[QuantityMissingDates])
Return
MEDIANX(_table, [_qtywithzeros])

[rajendraongole1]

Hi @scorbin-j - calculate the medianmeasure of the weekly issued amount for materials as below 

MedianWeeklyQuantity =
CALCULATE(
MEDIANX(
ADDCOLUMNS(
SUMMARIZE(
dimTime,
dimTime[ISO_WEEK],
BRIDGEMM[Materialfull]
),
"WeekSUM",
COALESCE([WeekSUM], 0)
),
[WeekSUM]
)
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!