cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KWallis
New Member

Calculated Column IF Amount Exceeds Dynamic Threshold

Hi, 

I am pretty new to Power BI, but I have the formula below to identify records that need Review by checking if the calculated Difference exceeds a Threshold. This formula works if I use it in a measure, but not with a calculated column. It incorrectly identifies rows as needing Review when it does not. The Threshold is a "WhatIf parameter" of whole numbers that users can change through a slicer. I want to use a calculated column because I intend to use Variance_check in a slicer to easily filter rows that require review.

 

Variance_check =

VAR Difference = CALCULATE(SUM('TB DATA'[Sales]), 'TB DATA'[Period] = "CurrentYear") - CALCULATE(SUM('TB DATA'[Sales]), 'TB DATA'[Period] = "PriorYear")

 

RETURN

IF(ABS(Difference) > Threshold[Value] , "Review" , "OK")

 

Any ideas on how to get this to work with a calculated column? Thank you in advance for the help!

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @KWallis 

You want to create a column but the column is calculated to change dynamically, right?

This seems to be impossible in Power Bi due to the difference between the measure and the calculated column. Columns are calculated and saved in the capture as soon as you create it, so the result in column won’t change when you do something on slicer.

But you can try to use measure to show the result which defined to display based on the slicer

Variance_check =
VAR Difference =
    CALCULATE( SUM( 'TB DATA'[Sales] ), 'TB DATA'[Period] = "CurrentYear" )
        - CALCULATE( SUM( 'TB DATA'[Sales] ), 'TB DATA'[Period] = "PriorYear" )
RETURN
    IF( ABS( Difference ) > Threshold[Value], "Review", BLANK() )

 

Then unselect show items no data

vchenwuzmsft_0-1634621562952.png

the result will behind the rows with blank。

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
KWallis
New Member

Great, thanks for all the suggestions.

v-chenwuz-msft
Community Support
Community Support

Hi @KWallis 

You want to create a column but the column is calculated to change dynamically, right?

This seems to be impossible in Power Bi due to the difference between the measure and the calculated column. Columns are calculated and saved in the capture as soon as you create it, so the result in column won’t change when you do something on slicer.

But you can try to use measure to show the result which defined to display based on the slicer

Variance_check =
VAR Difference =
    CALCULATE( SUM( 'TB DATA'[Sales] ), 'TB DATA'[Period] = "CurrentYear" )
        - CALCULATE( SUM( 'TB DATA'[Sales] ), 'TB DATA'[Period] = "PriorYear" )
RETURN
    IF( ABS( Difference ) > Threshold[Value], "Review", BLANK() )

 

Then unselect show items no data

vchenwuzmsft_0-1634621562952.png

the result will behind the rows with blank。

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Fowmy
Super User
Super User

@KWallis 

Measures can see the selection you make in the slicer or Parameter but Calculated Columns and Tables cannot see them because, adding columns and tables happens in the data model, once you add they are available to be used in the report. 

To achieve your requirement, you can assign the measure to the Filter Pane and Visual Filter and apply it accordingly to filter out the items dont need.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy, thanks for the response. This will work, however, I wasn't intending to expose the Filter pane to users, but I guess there really isn't a way out for now. Appreciate your response.

@KWallis 

The other way is to create new measures with required filters included but the option I suggested is easier. You can hide the filter pane from users or even hide even a single measure or a column from the users. Please refer to this article: 
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-filter


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors