pivot table formula

[alexpanjhc]

Hi 

I am having an issue with my pivot table formula.

here is my data:

as of date   Category          Final Status                    Description              M1        Book

4/30/2019   Long term     Markert Disc                         AAA                            0           100

   (null)                    other            other                             other                           -20           

4/30/2019   short term    short term                           AAA                              0           100

Sum({$<[Final Status]-={'Unrealized'}>}[Total M1])
-sum({$<[Instr Type]={'Other'},
[Description]={'Other'},
[Month Sold]={'Other'}, [As of Date]=>} total [Total M1]*isnull([As of Date]))

the total on the first line 04/30/2019 is correct, but when it comes down to the categories, the number not only is incorrect but repetitive, how do i fix it?

your input is highly appreciated.

[treysmithdev]

Using the TOTAL keyword will ignore all the dimensions not listed in between angled brackets: < >. So even if you drill down, you are still subtracting the overall total.

[alexpanjhc]

@treysmithdev 

thanks for your reply, but where do i use total?

i have used total in the set analysis. it is not working