Combining aggregating & iterating functions to calculate median


#1

Can someone please help me with this? I thought i understood this concept but cannot seem to put together the correct expression.

I am trying to aggregate the sum value of well cost in table ‘wvjobprogramphase’ for every well that is listed in table ‘wvwellheader’. I then want to iterate across the wvwellheader table so that I can calculate either the percentile or median value of all of the previous aggregates and want to display this value in the sub-total or card. I have nested a couple iterators while using the values() function but it appears that an average value is being displayed instead. Attached i have a scrubbed PBIX file and a screenshot of the final value.

Thanks in advance!

PBIscrub.pbix (102.2 KB)


#2

And think reviewing this technique here should get you want you need.

Iterating within and iterating function can be a bit of a mind cluster.

[Well Cost (Total) [$USD/Ft]]] = 
VAR DEPTH2 = SUMX(WVJOB, wvJob[TOTALDEPTHCALC])

VAR WELLCOST = 
MEDIANX(
    VALUES(wvWellheader[WELLNAME]),
    SUMX(
        VALUES(wvJob),
        CALCULATE(DIVIDE(SUM(wvJobProgramPhase[COSTACTUALCALC]),DEPTH2))
    )
)

I would attempt to simplify this like the example above


#3

Thanks Sam, you are a great teacher! I created the below expression, using the summarize function and was able to output the median value. See attached screenshot as a reference, but I was able to capture the P50 value on the card visualization and the subtotal under the table visualization.

When attempting to iterate through multiple tables, i find myself wanting to nest the functions together. My data model contains multiple tables with several layers to the hierarchy. After this working session, i am thinking nesting the iterators may not be ideal and I should be using this technique for my measures.

WELLCOST2 = 
MEDIANX(
	SUMMARIZE(
		WVJOBPROGRAMPHASE,
		WVWELLHEADER[WELLNAME],
		"PHASE WELL COST",
		SUM(WVJOBPROGRAMPHASE[COSTACTUALCALC]),
		"TOTAL DEPTH",
		SUM(wvJob[TOTALDEPTHCALC]),
		"CYCLE TIME",
		DIVIDE(SUM(WVJOBPROGRAMPHASE[COSTACTUALCALC]),SUM(wvJob[TOTALDEPTHCALC]))),
	[CYCLE TIME]
)


#4

Yes I would say so.

I rarely nest iterating functions just due to the complexity of the calculation. It can sometimes confuse me! So I look to go the simplest route I can think of that returns the right answer