Convert measure to calculated column for use in filters and slicers

[rexii23]

Hi

I’m trying to create a scenario analysis page in PBI. The data consist of projects and their forecast spend each financial year. The projects are ranked based on their importance.

For the analysis, the projects are ranked and the cumulative spend of the projects by rank can not exceed a cut-off dollar value.

Using slicers I can select the financial years applicable for analysis and also the cut-off amount.

I’ve created a measure that correctly calculated the cumulative spend and also another measure if it will be cut-off

This all displays great in a table. However to visualise the data, what I want to do is to turn the ‘is cutoff’ measure into something like a calculated column so it can be used in a slicer or filter.

For example, I would love to colour the chart to show that the bottom 4 projects in the list will be cut. In essence, a waterline type of chart.

I’ve updated a pbix with sample data to dropbox here. https://www.dropbox.com/s/71degno91axmdcj/sample%20data.pbix?dl=0

Can someone please give me a hand? Thanks!

[sureshnair_123]

I have  created one measures with some formula like  example

a=sum(b)
i have created another column with the same formula again

a=sum(b)

measure is working great and showing exact values but when coming to the column it is not working as expected, as i see measures will work on filter context and column is working on row context, is there any way to fix this??

i need only column becoz i cannot place this measure on x axis in line graph

thanks in advance

[RoopaR]

Probably late but might help others in a similar situation. For a measure an aggregate function is introduced. This is not needed for a calculated column.

For ex a measure ----- SomeValue=sum(Table(sales))/60

Same calc column ---- SomeValue=(Table(sales))/60

[blaw2]

Absolutely helped this newbie out!! I appreciate that you had taken the time to add your answer long after the original post to help others. Thank you!

[Anonymous]

Hi Rexii,

Here is an video of using Measures in filters/slicers

https://community.powerbi.com/t5/Video-Tips-and-Tricks/Turn-Measures-On-and-Off-Inside-a-Chart-with-...

Could this be of any assistance?

[rexii23]

This is a good trick, I've actually used something very similar to get my cutoff slicer to work. It however doesn't help with turning a measure into something I can use in a filter for a chart or be able to place it into a slicer.

[rexii23 · Accepted solution]

Found a way to get what  I need from this post  http://community.powerbi.com/t5/Desktop/How-to-create-a-static-column-or-measure-and-use-it-in-slice...

Instead of trying to create a calculated column from a measure, I just created a new measure using the 'is cutoff' measure. so in summary

**bleep** Sum = IF(HASONEVALUE(project[Prj Code]),
CALCULATE(
	 SUM(spend[Amount]), FILTER(
		ALLSELECTED(project),
		project[Rank] <= MAX(project[Rank]))
)
)

is cutoff = IF([**bleep** Sum] >[Selected cutoff], 1, 0)

Above cutoff = IF([is cutoff] = 0, SUM(spend[Amount]), BLANK())

Below cutoff = IF([is cutoff] = 1, SUM(spend[Amount]), BLANK())

I then put both measures onto the same bar chart, to have a dynamically changing chart based on my two slicers.

[v-ljerr-msft]

Hi @rexii23,

Great to hear the problem got resolved! Could you accept your helpful reply as solution to help others who may also have similar issue easily find the answer and close this thread?

Regards

[TristanKuesters]

I didn't look at your example, but is it not possible to create the calculated column on the project table? Then the row filter context will be the project - and thats what your looking for?

[rexii23]

When I tried to create a calculated column on the project, the values did not change when changed the selection on the slicers.