Median & Average measures in report using data through different tables

[Antonis13]

Hello all,

I want to create a calculation for Median & Average values in order to compare different Projects.

In first phase of creating the report I tried the Union Function and the medianx/averagex method applied to 6 different tables-projects but it seems that I cannot combine them correctly.

Here you can see sample data of what I am trying to perform:

Project1

Cost Category	Total Cost in Eur	Cost per Unit
Design	55000	5,5
Permitting	120000	12
Construction	803000	0,008

Project2

Cost Category	Total Cost in Eur	Cost per Unit
Design	12000	1,2
Permitting	0	0
Construction	330000	0,0033

.....

Project6

Cost Category	Total Cost in Eur	Cost per Unit
Design	66000	6,6
Permitting	3200	0,32
Construction	310000	0,0031

Can you please help calculate the Average and Median values per row (e.x. Design, Permitting....) ignoring 0 values in each occasion?

Below you will see my current output that I want to add median & average values per cost category (for example A.Engineering/Design Average: xx,x€ Median: xx,xx€) and recalculating each time values change

Thanks in advance for your suggestions!

[Antonis13]

It seems that I am not able to properly apply Union. I should apply Union in a new calculated table or a measure?

[lbendlin]

That depends. Can your results be impacted by users interacting with the report?

[Antonis13]

Yes the results can be impacted and should be impacted

[lbendlin]

Then the UNION has to be an intermediate step in your measure calculation.

[Antonis13]

Can you please explain in detail how to do it?

[lbendlin · Accepted solution]

[lbendlin]

That should work fine. Is your UNION a calculated table or part of a measure?