Power pivot - Calculate Subtotal from a measure and repeat it in a column

0
votes

I have created a measure in Power Pivot and I am able to calculate subtotal from it and repeat the values in another column as shown below. The problem is that it doesnt take into account the external filters on the pivot table.

Table1

Here are the steps -

1) I created a measure to calculate Previous Day Amount -

*Table1 contains all the data

*Table2 is the Date table

*Table1 and Table2 are linked

PREV-DT-AMT= CALCULATE(SUM(Table1[NET-AMOUNT]), FILTER(all(Table2[DT]), Table2[DT] < max(Table2[DT]) ))

2) In this step I calculate subtotals shown in above table under column "PrevDayST"

=calculate([PREV-DT-AMT], allexcept(Table1,Table1[CLIENT],Table1[Type],Table2[DT]))

The issue here is that when I use filters on the pivot table to filter out some Sub-Types the subtotal measure in step2 doesnt take into account the filters in the pivot table. I understand that allexcept ignores all the external filters. I would highly appreciate if you could give me a solution that will let me calculate subtotal from measure [PREV-DT-AMT] and repeat it under "PrevDayST" WITHOUT ignoring external filters.

I have been struggling with this for the past couple of days. Thanks in advance.

1
excelpivot-tablepowerpivotdax
Why do you have each day in a column instead of date as a column? – eshwar
Eshwar, Although, I have just shown three days the requirement is to project cashflows for the next 10days. There are lot more columns than what I actually showed in the table which are used for comparison and its best if we show them as day columns rather than having a date column. – Sharat

1 Answers

0
votes

I used allselected finction and it worked - [PREV-DT-AMT],ALLSELECTED(Table1[SUB-TYPE]).

Regards, Sharat