Sum of Median

[BillPoster]

I have a table that shows median time for each operation (multiple operations) in  a matrix and this shows correctly. 
Measure

MedianX Of Operation = MEdianX(Values(Append1[Batch Number]),  CALCULATE(Sum(Append1[Duration (HOURS)])))

Now the issue i have is showing the total at the bottom of the column as the sum of all the median values in the column. However it gives the median value instead.  (As seen in the snipped image below) 

[BillPoster]

A big thank you to SpartaBI for all his help on this, he worked out the answer and got the data showing as I wanted using the following measure. 

MedianX Of Operation = sumx(SUMMARIZE('Appended Months','Appended Months'[Area],'Appended Months'[Final Description]),Calculate(MEdianX(Values('Appended Months'[Batch Number]), CALCULATE(Sum('Appended Months'[Duration (HOURS)])))))

[v-yalanwu-msft]

Hi, @BillPoster ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BillPoster]

Hi, Not yet, I am going to have a zoom chat with someone about this to see if we can figure it out this week. I will report back how it goes. 

[v-yalanwu-msft]

Hi, @BillPoster ,

I'm sorry that it is really difficult to understand the final result you want. The result you want cannot correspond with the data you provided above, so I can hardly understand your logic. It would be nice if you could share a simple example file and the result

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi， @BillPoster ;

You could create a measure.

Measure = SUMX(SUMMARIZE('Table',[Batch Number],[Operation Description],"value",[MedianX Of Operation]),[value])

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BillPoster]

Hi, This just sums up all the values, rather than just the median values. 
In your image above you are showing job one 3 times and a median time of job one each time.  It has to show the median time for each Job only once. 

[BillPoster]

Hi SpartaBI, Firstly thanks for taking the time to answer.

To explain the data a bit better

There are around 70 operations in total and Every batch has each of those operations completed on it. (There can be multiple instances of an operation being done for a batch before it is completed.)
As far as I am aware the measure sums up the values for each batch number then gives the median value for that operation. And this does work on a line by line basis, however I am trying to get a total of all median values as well as I would like to use this value in a graph as well. 

Your measure gives me incorrect median values for each operation and the total at the bottom also isn't showing what I want it to.  (To the left of the image is the description of each operation.)

[SpartaBI]

@BillPosterI still don't quite understand your data. Maybe you could share the file? 
From what I can see now is that you have operation number on the rows.
If according to your data the per row is good so the total should represent the median of the rows you put in the matrix when you use MEDIANX. In this case it's Operation number and maybe this will work:

MedianX Of Operation = SUMX(Values(Append1[Operation Number]),  CALCULATE(MEDIAN(Append1[Duration (HOURS)])))

 Hard to say, cause didn't understand fully your model.

[BillPoster]

Data is confidential so cannot share in its present form however I have reworked a small sample but unable to attach file as my organisation does not allow me to share one drive link. 
Below is a sample of one part of the data.  
To try and break down a bit more.
We create an object and that object is made up of 3 parts in 3 areas.
A batch number is issued for each object and it is the same for all 3 parts of that object.
An order number is issued for each part of the object so a batch number has 3 order numbers assigned to it.
Operation Numbers are duplictaed as it is just the flow within that area 
Duration is the time accrued each time it is worked on.
Operation description is unique and not duplicated in any of the areas (However this is a calculated column in PowerBi as there has had to be some reworking of the data so there are no duplicated descriptions) 

So what i need is something that shows the median duration of Operation description  (The current measure I have works for this is a table)
I also need a measure that will work out the total of all the median values
So if  the median of Job one is 0.78 and median of job 2 is 0.95 and  median of job 3 0.77 it would show 2.5

Hopefully this is a bit clearer than mud 😀

Order Number	Operation No.	Operation Description	Duration (HOURS)	Op  DATE	Batch Number
10003072	00-0280	Job Three	0.593888884	01-Oct-21	333400
10003080	00-0220	Job One	3.768333303	01-Oct-21	146500
10003080	00-0220	Job One	3.757777748	01-Oct-21	146500
10003080	00-0220	Job One	3.688333304	01-Oct-21	146500
10003080	00-0220	Job One	3.493055528	01-Oct-21	146500
10003080	00-0220	Job One	3.775555525	01-Oct-21	146500
10003080	00-0220	Job One	3.619444416	01-Oct-21	146500
10003081	00-0220	Job One	4.291666632	01-Oct-21	333000
10003081	00-0220	Job One	4.298055521	01-Oct-21	333000
10003081	00-0220	Job One	4.265833299	01-Oct-21	333000
10003081	00-0220	Job One	4.227499966	01-Oct-21	333000
10003081	00-0220	Job One	3.484444416	01-Oct-21	333000
10003081	00-0220	Job One	3.621666638	01-Oct-21	333000
10003081	00-0220	Job One	3.476111083	01-Oct-21	333000
10003081	00-0220	Job One	3.489166639	01-Oct-21	333000
10003081	00-0220	Job One	1.488888877	01-Oct-21	333000
10003081	00-0220	Job One	3.36666664	01-Oct-21	333000
10003081	00-0220	Job One	3.342499973	01-Oct-21	333000
10003081	00-0220	Job One	3.318333307	01-Oct-21	333000
10003081	00-0220	Job One	3.31166664	01-Oct-21	333000
10003081	00-0220	Job One	3.359444418	01-Oct-21	333000
10003122	00-0280	Job Three	2.163333316	02-Oct-21	147100
10003122	00-0280	Job Three	2.088888872	02-Oct-21	147100
10003122	00-0280	Job Three	2.17027776	02-Oct-21	147100
10003122	00-0280	Job Three	2.151944427	02-Oct-21	147100
10003124	00-0280	Job Three	0.510277774	02-Oct-21	333600
10003124	00-0280	Job Three	2.500833313	02-Oct-21	333600
10003124	00-0280	Job Three	2.585833313	02-Oct-21	333600
10003125	00-0220	Job One	4.954722183	08-Oct-21	245200
10003125	00-0220	Job One	0.298333331	08-Oct-21	245200
10003125	00-0220	Job One	3.855555525	08-Oct-21	245200
10003125	00-0220	Job One	3.632777749	08-Oct-21	245200
10003125	00-0220	Job One	2.084722206	08-Oct-21	245200

[SpartaBI]

@BillPosterStill don't understand the business :))
Can you share what is the desired result from your sample data?
For example I took all columns and a median of the hours and it looks like this:

In the sample data it looks like each batch had one order number and one operation no. Not sure this is the real situation in your data.
Anyway, for the sample data, what is the visual you want to do and the desired result? 🙂

[BillPoster]

I want to show the sum of all the medians visible at the bottom of the Table. 
I also want to be able to show the sum of the median in a graph against an expected value. 
As it stands at the minute, If I show the data for Area 1 it works, but as soon as I add in data for area 2 or 3 it throws the graph data out. 
Area 2 selected Left is the Expected and right is the median total for all operations

As soon as I add another area it doesn't work.

This is replicated in the table as well, so with a filter for one area selected it works and as soon as you introduce another area it throws it all out. 

What little hair i had left is going quickly 😀

Could it be down to some of the data being reword in a calculated column?

[SpartaBI]

@BillPoster I will pm you 🙂

[SpartaBI · Accepted solution]

@BillPoster it should be the other way arround. 
In order to get the sum of the middle step in the total you should use SUMX outside and inside the median.
Something like:

MedianX Of Operation = SUMX(Values(Append1[Batch Number]),  CALCULATE(MEDIAN(Append1[Duration (HOURS)])))

The problem is I don't understand how you got the right number per row with the sum, unless it's just one row for each batch number in the model table.
 

Showcase Report – Contoso By SpartaBI