cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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?

1 ACCEPTED SOLUTION
Community Support

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 =
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.

3 REPLIES 3
Community Support

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 =
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.

Helper I

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

```Median = var _table =
Return
MEDIANX(_table, [_qtywithzeros])```
Super User

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

MedianWeeklyQuantity =
CALCULATE(
MEDIANX(
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!

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.