cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rexii23
Advocate I
Advocate I

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

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.

View solution in original post

9 REPLIES 9
sureshnair_123
New Member

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

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

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
Not applicable

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?

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.

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.

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

TristanKuesters
Resolver I
Resolver I

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?

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors