 # Convert a measure to a calculated column

Hi,

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,

Thanks

Dan

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.

Here’s the DAX that does the virtual table lookup:

Total Sales Lookup =

``````VAR vTable =
CALCULATETABLE(
FILTER(
SUMMARIZE(
Sales,
Sales[Customer ID],
Customers[Customer Name]
),
"TotSales",  [Total Sales]
),
Sales[Customer ID] = [Harvest Rank Slicer]
)
)

RETURN
MINX(
vTable,
[Total Sales]
)
``````

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.

2 Likes

Hey Brian,

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

Sigma Test4.pbix (969.8 KB)

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:

``````Prob Lookup =

VAR zLookupVal =
ROUND(
ABS( [Z Lookup] ),
2
)

VAR vTableProb =
CALCULATETABLE(
FILTER(
'Frequency Distribution',
'Frequency Distribution'[X Values] = zLookupVal
),
ALL('Frequency Distribution'[X Values] )
)

RETURN
MINX(
vTableProb,
'Frequency Distribution'[Probability Area]
)
``````

Hope this gets you what you need. Full solution file posted below.

Wow thats pretty cool Brian,

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.

Prob Lookup =

``````VAR zLookupVal =
ROUND(
ABS( [Z Lookup] ),
2
)

VAR vTableProb =
CALCULATETABLE(
FILTER(
'Frequency Distribution',
'Frequency Distribution'[X Values] = zLookupVal
),
ALL('Frequency Distribution'[X Values] )
)

VAR Prob =
MINX(
vTableProb,
'Frequency Distribution'[Probability Area]
)

RETURN
SWITCH( TRUE(),
[Z Lookup] > 3.09, 1,
[Z Lookup] >= 0, Prob,
[Z Lookup] < -3.09, 0,
[Z Lookup] < 0, 1-Prob,
-9999
)
``````

Jeez thanks Brian.

You guys make it look so easy. I thank you for helping me and allowing em to learn from you.

Cheers

Dan

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.

• Brian

2 posts were split to a new topic: Dynamically Adjusting Slicer Values Based on Other Measures

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.

Thanks very much.

– Brian

Hi @BrianJ, thank you for raising this, posts #9 and #10 has been moved to a new topic. Here is the link for reference: Dynamically Adjusting Slicer Values Based on Other Measures.