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)
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
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]
)
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
