Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

Pivot Table Default Total showing wrong

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])
)

 

Pivot Total.png

 

1 Solution

Accepted Solutions
crusader_
Partner - Specialist
Partner - Specialist

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

View solution in original post

3 Replies
rogerpegler
Creator II
Creator II

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
Creator II
Creator II
Author

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.

Pivot total option.png

crusader_
Partner - Specialist
Partner - Specialist

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