Median of values from multiple tables

[sy898661]

Hi!!

I am trying to do something, and I dont know if it is possible, but would love some insight from all the pros here 🙂

I have a report, and it has 7 tables/queries within it (all from different excel files).

1. Product 1
2. Product 2
3. Product 3
4. Product 4
5. Product 5
6. Summary
7. Month

In each Product table, there are lot numbers, start and end dates for each lot, and a calculated column for Lot Release Cycle Time (DATEDIFF between start and end).

The Product tables have no relationships to one eachother, although I did create the Month table that holds a single column of dates from 1/1/2017 - 12/31/2020. I used a 1:* relationship to each start date in the Product tables. This allowed me to put data from each Product in a single bar chart visual.

The Summary Table has multiple columns (1 row) that store the Average Lot Release Cycle Time (LRCT) for each product, and a final calculated column that has the Overall Average LRCT.

One measure we really want to figure out is Overall MEDIAN LRCT. I am trying to figure out the best way to do this because I can't do what I did for the Overall Average, and thought of a possiblity (I just dont know how to execute it--or if it is possible)

1. Create new table and pull in LRCT from all Products into a single column (Merge?)

I hope this makes sense, I can't upload my file directly because of IP issues. 

But can anyone help me in finding the Overall MEDIAN of all Product tables?

Thank you so much!!!!

[v-yuta-msft]

@sy898661 ,

Could you please share the sample data and give the expected result?

Regards,

Jimmy Tao

[sy898661]

Hi @v-yuta-msft !

I have thrown together a really simple version of my file. The only difference is in the sample file, all Product queries are set up the same way, in the OG file each Product query is set up differently (differnt column names).

here is a link to a google drive file, let me know if this doesnt work idk how else to share the file!

https://drive.google.com/file/d/14_j4FRmBxcRyoKPHxg2inSuAvtJRWoI9/view?usp=sharing

I wanted to be able to find the overall median value, but also wanted to be able to see the overall average and median per month (added as lines in the charts)

Is this possible?

Thank you so much!!!