Sum in straight table is not matching with data

[50ShadesOfSalty]

Hi everyone,

While i'm developing a straight table, I found out that the Sum that is being displayed is quite different from the actual data.

Some more information:

Dimension:

- Synthetic Dimension: Valuelist('ExampleA')

- Month Year (MMMYYYY)

Measure: 

- if(Valuelist('ExampleA') = 'ExampleA', Sum({< [Year]={"$(=Max(Year))"}, [SearchString]={"*String1*"} >}[Interactions]))

Imagine, the straight table give me 103 for January. However, if i create a pivot table with all those dimensions (Country, SearchString Month Year) and Export it to a excel, the total is still the same as the Sum(103), but the sum of all the values in the rows for January give me 475. 

Is this a aggr problem?

Any tip would be highly apreciated!

Thanks for the help!

[lorenzoconforti · Accepted solution]

It might be an aggregation problem; try this:

sum(aggr( Sum({< [Year]={"$(=Max(Year))"}, [SearchString]={"*String1*"} >}[Interactions]), Country, SearchString, MonthYear))

[50ShadesOfSalty]

It is the correct answer.

However, let me just add that there is currently a issue when we try tu work with AGGR and Synthetic Dimensions.

Thanks for the help!

[lorenzoconforti]

I don't think there is. You need to keep in mind that, when calculating totals, the engine needs to work through a data table made of dimensions and measures. If those dimensions do not exist in your data (because you are using a synthetic dimension) then you'll need a way to re-creating that data structure for the engine to work as expected