Pivot Table Default Total showing wrong

[mahitham]

Hi Experts,

In the below pivot table I am getting wrong default totals. Please help me to fix the issue. I have used below expressions.

Measure1 : 

(
Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[Name]={'A','B'}>}[Amount])
/
Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[Name]={'A','B'}>}[ID Count])
)

Measure2 : 

(
Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[Name]={'C'}>}[Amount])
/
Sum({<[Date]={">=$(=addmonths(today(),-12))"},[Type]={'Level1'},[Name]={'C'}>}[ID Count])
)

[rogerpegler]

In the Measure properties, try change the Total from Auto to SUM.

As an aside - having Today() in the measure can cause performance problems as it has to reevaluate every time anything happens.  Instead, load Today() into a variable in your load script  and use the variable in your set analysis.

[mahitham]

Hi @rogerpegler 

I am using Pivot Table. I haven't found the sum option under Total. By modifying Total also no luck still same issue.

Is there any way to include this Sum in by expression above to fix this Total.

[crusader_ · Accepted solution]

Hi,

In your expression you're calculating RATIO, so your Total calculated as SUM(TOTAL {<your conditions>} Amount) / SUM(TOTAL {<your conditions>} ID_Count).... which is different to sum values from cells...

To fix your total you need use aggr... it won't affect individual cell's values, however will fix total...

Sum(Aggr (SUM({<your conditions>} Amount) / SUM({<your conditions>} ID_Count),[Month Year]) )

Hope this helps.

//Andrei