cancel
Showing results for
Did you mean:
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
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
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?

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.

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?

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.

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.

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?

Regards