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.
