Total Sum of Value excluding slicer filter.

[Newbie22]

Hi!

I have one table and created 3 measures.

1. Beginning Balance Total = SUM('Table'[Beg Balance Amount])

2. Daily Balance =  SUM('Table'[USD Amount])

3. Remaining Balance = [Beg Balance Total] - [Daily Balance]

When I put it in a table and use a slicer for filter,  the result is not what I need because Beginning Balance total should show the overall amount of Beginning Balance Amount regardless of the Reporting Date. Filter in the slicer should not affect this measure. 

In that case, I need measure "Beginning Balance Total" to be the overall Beg Balance Amount per collector.

That means in the above example. If Slicer is set to 04/01/2023 the period would be April 2023 and then Collectors are AA and CC so the total Beg Balance amount to appear should be $0 for AA because AA doesn't have an equivalent invoice in the Beginning Balance Amount on Period April (see below screenshot for reference) and $1900 for CC  (Invoice # 111005 is 400 and Invoice # 111006 is 1500. 400 +1500 = 1900). 

Below is my data:

Desired output should be something like this:

Here's the pbix in case you need to check and play around: Sample PBI - Google Drive

Thank you so much to those who will help 🙂

[Newbie22 · Accepted solution]

Hi All,

I already found out the right DAX. 

Sharing below formula in case someone have the same issue. 

Beg Balance Total = SUMX(SUMMARIZE('Table', 'Table'[Period], 'Table'[Collector]), CALCULATE(SUM('Table'[Beg Balance Amount]), ALL('Table'[Reporting Date])))

I used Summarize to Group the data by period and by collector. 

[Greg_Deckler]

@Newbie22 Should be able to use CALCULATE along with REMOVEFILTERS or possibly ALLEXCEPT or just use this:

Beginning Balance Total = SUMX(ALL('Table'),[Beg Balance Amount])

[Newbie22]

Hi @Greg_Deckler , I didn't work. ☹️  It shows the whole Beg Balance amount for every period. I need the total of beg balance per collector and based on the Period of the Reporting Date (slicer).