CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Skyler
Skyler

Reputation: 59

How to make total value constant with changing filters in Power BI

I am really new to DAX and Power BI, and hopefully, you guys could help me out with my question. Any tips, feedback, and comments are greatly appreciated!

This is my scenario:

I am trying to make a total to remain constant (highlighted in orange) to be used as part of another column's calculation - Total Parcel Credited. Here is my general data, which has two of the filters applied as "Select All".

enter image description here

Regardless of how I perform filter (filter1, filter1+filter2 or just filter2), I need my total to always remain constant.

When one of the filter 2 is applied enter image description here

When another filter 2 is applied enter image description here

When both filters are applied enter image description here

This is what I have to achieve the individual company figures.

Total Parcels Ordered = 
calculate(
sum(
Orders[ParcelOrdered]
    ),
ALL(Orders[CreditReason]))

But in doing so, the total does not stay constant. I have also tried creating a few measures to summarize the column, resulting to a constant value of 650 on Total Parcel Ordered. This will allow me to perform the Total Parcel Credited column calculation correctly, regardless of any filter applied. But using the filter1 (Companies) still shows 650 against the Mail Company A. How do I make the total to remain constant, regardless of what I choose in my filter, while it can be still be used in my other columns?

Thanks in advance if anyone could help me out in this. Thanks for your understanding as well.

Upvotes: 1

Views: 3792

Answers (1)

RADO
RADO

Reputation: 8148

You need to remove filters either from the entire table, or from all columns that provide the filters:

Total Parcels Ordered = 
CALCULATE(
  SUM( Orders[ParcelOrdered] ),
  ALL(Orders)
)

or:

Total Parcels Ordered = 
CALCULATE(
   SUM( Orders[ParcelOrdered] ),
   REMOVEFILTERS(Orders)
)

or (if you only plan to use these 2 slicers and Company is a column in your orders table):

Total Parcels Ordered = 
CALCULATE(
  SUM( Orders[ParcelOrdered] ),
  ALL(Orders[CreditReason], Orders[Company])
)

Upvotes: 2

Related Questions