Could some one help me convert this working measure to the same result just as a calculated column?

This measure works perfectly the issue is i want to use the result as a LOOKUPVALUE which cant be done via a measure so i want to convert it but its not working.

This is the measure that working fine

Z = (‘Normal Distribution Chart’[Lower & Upper Sigma Level Input] - [Mean (μ)]) / [Standard Deviation (σ)]

However when i try this is in calculated column i get this result.

Z Column = (‘Normal Distribution Chart’[Lower & Upper Sigma Level Input] - [Mean (μ)]) / [Standard Deviation (σ)]

When i break the formula down its not using the mean or std dev in the table its using the mean and std dev of the total data set i must need to add some filters in there but cant figure out where,

You’re right that you can’t use a measure as the result column within LOOKUPVALUE(), but you can mimic the same functionality by putting your measure into a virtual table, and then filtering it by the same search criteria you would use in the LOOKUPVALUE function. Here’s an example I created using Customer ID to do a virtual look up on a total sales measure.

Given that your measure is working perfectly as is, I thought this might be a more attractive approach than rewriting it. However, if you still want to do it is a calculated column, please post your PBIX and I’d be glad to give it a go.

I hope this is helpful. Full solution file posted below.

Thats a pretty cool technique! im not sure it will work for my scenario though.

I have attached my PBIX.

What i`m trying to do here is when i enter a value into the SAP minutes slider, I want to look up that number in the Normal distribution table > Generated series column and give me the Z value. From there i then want the Z value result to look up the same value in the Frequency distribution table and return me the Probability area result.

Example 469 mins on the slider is Z of 0.01 Which is 0.01 in the X values which is a Probability area of 0.50 or 50% chance of someone completing the task of that time.

I would normally do this but cant use the measure

Z Value Look up = ROUND(LOOKUPVALUE( Z , Generated Series , ‘SAP Minutes Slider’[SAP Minutes Value] ),2)

Then use this result look up to find the probability of the X value

Probability =

IF( [Z Value Look up] = 0, LOOKUPVALUE( ‘Frequency Distribution’[Probability Area], ‘Frequency Distribution’[X Values], [Z Value Look up]),

LOOKUPVALUE(‘Frequency Distribution’[Probability Area], ‘Frequency Distribution’[X Values], ABS([Z Value Look up])))

See what you think, but as I understand your requirement I think it’s a great fit. Your calculated normal distribution and frequency distribution tables already do the bulk of the heavy lifting. Just takes two additional measures:

Measure #1 uses the harvested slicer value to look up the z value from the normal distribution table:

Z Lookup =
VAR vTableNorm =
CALCULATETABLE(
FILTER(
'Normal Distribution Chart',
[Generated Series] = [Harvest SAP Minutes]
),
ALL( 'Normal Distribution Chart'[Generated Series] )
)
RETURN
MINX(
vTableNorm,
[Z]
)

Measure #2 uses the result of Measure #1 to look up the probability value from the frequency distribution table:

One thing is wrong though if the Z value is negative i need it to recognize that and make the calculation by doing for example 1 - the Z value to give the correct probability. in this case it would be 1 - 0.99 = 0.01 or 1% chance

See below it doesn’t make sense that 19 SAP minutes is 99% probable it will be painted in that time.

i actually wrote this incorrectly in my last post should be if is greater than 0

Probability =

IF( [Z Value Look up] > 0, LOOKUPVALUE( ‘Frequency Distribution’[Probability Area], ‘Frequency Distribution’[X Values], [Z Value Look up]),

LOOKUPVALUE(‘Frequency Distribution’[Probability Area], ‘Frequency Distribution’[X Values], ABS([Z Value Look up])))

Simple fix. We just add a SWITCH( TRUE() ) construct at the end to test the sign and value of [Z Lookup] and return the correct probability. As written, this will also take care of any out of bounds errors when your slider produces an absolute value of Z outside of your frequency distribution lookup table.

My pleasure – glad that got you what you needed. It’s a really interesting report you’ve put together, and I enjoyed the opportunity to play around with it.

Would it be possible for you to open a new topic entitled “Dynamically Adjusting Slicer Values Based on Other Measures” and move posts #9 and #10 currently in this thread to that new topic? This is a fundamentally new issue raised, which I think others likely would search on in the future. Having it as a separate topic will make it easier to find.