cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rexii23 Frequent Visitor
Frequent Visitor

Convert measure to calculated column for use in filters and slicers

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!

1 ACCEPTED SOLUTION

Accepted Solutions
rexii23 Frequent Visitor
Frequent Visitor

Re: Convert measure to calculated column for use in filters and slicers

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.

6 REPLIES 6
TristanKuesters Regular Visitor
Regular Visitor

Re: Convert measure to calculated column for use in filters and slicers

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 Frequent Visitor
Frequent Visitor

Re: Convert measure to calculated column for use in filters and slicers

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

mravestein Frequent Visitor
Frequent Visitor

Re: Convert measure to calculated column for use in filters and slicers

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 Frequent Visitor
Frequent Visitor

Re: Convert measure to calculated column for use in filters and slicers

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 Frequent Visitor
Frequent Visitor

Re: Convert measure to calculated column for use in filters and slicers

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 Super Contributor
Super Contributor

Re: Convert measure to calculated column for use in filters and slicers

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?Smiley Happy

 

Regards