cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaydonguyen
Regular Visitor

Fixed Calculations in PBI

 

 

I am converting a tableau workbook to PBI.

 

is there a way to perform a fixed calculation in PBI?

 

Below is the link to the fixed calculation function in Tableau.

Tableau fixed calculation 

1 ACCEPTED SOLUTION
danextian
Resident Rockstar
Resident Rockstar

hi @jaydonguyen ,

When you say fixed calculation, you mean the result of a calculation doesn't change no matter what filter you choose? You can do something like:

calculation =
CALCULATE ( SUM ( Table[Column] ), ALL ( Table ), ALL ( Table2 ) )

This fixes the result of an expression regardless of the filters applied to the tables in the model that are included in the formula. Or you can just fix the calcuation for a particular column. The formula below removes any active filteres to Column2 but will be filtered if filters are applied to any other column. 

calculation =
CALCULATE ( SUM ( Table[Column] ), ALL ( Table[Column2] ) )

There's a good read in sqlbi - https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept.... If you are new to this, though, it will take sometime to digest it. 

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @jaydonguyen ,

 

If you need to keep a measure or column in a fixed value, you may use ALL function in the formula like DAX below. Otherwise, you can use ALLSELECTED function to take effect of any slicer or filter.

 

Measure1=CALCULATE(SUM(Sale[Sales]), ALL(Sale))

 

Measure1=CALCULATE(SUM(Sale[Sales]), ALL(Sale[Column1]))

 

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

danextian
Resident Rockstar
Resident Rockstar

hi @jaydonguyen ,

When you say fixed calculation, you mean the result of a calculation doesn't change no matter what filter you choose? You can do something like:

calculation =
CALCULATE ( SUM ( Table[Column] ), ALL ( Table ), ALL ( Table2 ) )

This fixes the result of an expression regardless of the filters applied to the tables in the model that are included in the formula. Or you can just fix the calcuation for a particular column. The formula below removes any active filteres to Column2 but will be filtered if filters are applied to any other column. 

calculation =
CALCULATE ( SUM ( Table[Column] ), ALL ( Table[Column2] ) )

There's a good read in sqlbi - https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept.... If you are new to this, though, it will take sometime to digest it. 

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors