Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
It seems that I am not able to properly apply Union. I should apply Union in a new calculated table or a measure?
That depends. Can your results be impacted by users interacting with the report?
Yes the results can be impacted and should be impacted
Then the UNION has to be an intermediate step in your measure calculation.
Can you please explain in detail how to do it?
That should work fine. Is your UNION a calculated table or part of a measure?
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |