cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duncanh
Helper I
Helper I

Calculate total with filters in place

This should be super simple but I can't get it to work. I need to calculate the total check amount where if I filter for Ice Cream, I should get all of the item total for the checks that Ice Cream appeared on.

 

Here is an example of my data:

duncanh_0-1623879282640.png

So if I have ice cream selected in a slicer, I need the total of checks 1 and 2, and not check 3.

Ideal output: 23 (totals of checks 1 and 2)

More details:

duncanh_0-1623886176898.png

I pretty much need that second column to equal the third column when the second column is populated. The third column is calculate(sum(amount), all(item)) . The second column is just a sum of the amount. 

 

As always all help is appreciated!

 

1 ACCEPTED SOLUTION
PaulOlding
Solution Supplier
Solution Supplier

This measure gives you the expected output of 23.

I'm not clear that it'll work on your actual data though.  Your screenshot has an additional field, discountjoin, that isn't explained where it fits in the model.

 

Check Total =
VAR _InscopeChecks = VALUES('Check Items'[Check number])
VAR _Result =
CALCULATE(
SUM('Check Items'[Amount]),
REMOVEFILTERS(),
_InscopeChecks
)
RETURN
_Result

View solution in original post

4 REPLIES 4
PaulOlding
Solution Supplier
Solution Supplier

This measure gives you the expected output of 23.

I'm not clear that it'll work on your actual data though.  Your screenshot has an additional field, discountjoin, that isn't explained where it fits in the model.

 

Check Total =
VAR _InscopeChecks = VALUES('Check Items'[Check number])
VAR _Result =
CALCULATE(
SUM('Check Items'[Amount]),
REMOVEFILTERS(),
_InscopeChecks
)
RETURN
_Result

View solution in original post

AlexisOlson
Community Champion
Community Champion

This is what the function ALLSELECTED is generally used for.

 

Your total with slicer filtering might look like this (or not depending on exactly what filters you want to include/exclude):

CALCULATE ( SUM ( Data[Amount] ), ALLSELECTED () )

 

Thanks for the input. I don't think this is what I need. I added allselected(item) to the sum and it returns the exact same thing as just a regular sum. I added some more details in the description. Maybe that clears things up

AlexisOlson
Community Champion
Community Champion

You didn't try quite what I suggested then. If you write ALLSELECTED ( Data[Item] ), then only the filter context on the [Item] column is reset to the slicer selection but the local filter context from e.g. [discountjoin] is still in place. Since I don't know what tables you have in your model, I suggested the broadest use of ALLSELECTED (without any table or column arguments). If this is too broad, you can modify it accordingly but I suspect applying on that single column may be too narrow.

Helpful resources

Announcements
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

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors