I was looking everywhere but found no solution. I have a problem because I need to show all the data in a PivotTable, no matter what the user selected in the filter.
Sum ({1}Aggr ( Sum({1<Field1={'X'},Field2={'Y'},Field3={'A','B'}>} distinct VALUE), Field4, Field5, Field3) )
In KPI it works well, if user selects Field3 = A then in KPI with the formula:
Sum ({1}Aggr ( Sum({1<Field1={'X'},Field2={'Y'},Field3={'B'}>} distinct VALUE), Field4, Field5, Field3) )
is used, it will display the data with B anyway.
and when formula is
Sum ({1}Aggr ( Sum({1<Field1={'X'},Field2={'Y'},Field3={'A','B'}>} distinct VALUE), Field4, Field5, Field3) )
and user select Field3=A, in KPI we always show sum A+B
The problem is when I add these formula
Sum ({1}Aggr ( Sum({1<Field1={'X'},Field2={'Y'},Field3={'A','B'}>} distinct VALUE), Field4, Field5, Field3) )
to the pivot table and set Field3 as the column.
When the user selects the filter Field3 = A, column B is null (no data in this column).
Is there any way for this to work with all kinds of charts and tables where we also use dimensions?
Can you please share some sample data set to demonstrate the issue?
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful