topic Sum in straight table is not matching with data in App Development

Sum in straight table is not matching with data

50ShadesOfSalty — Sat, 16 Nov 2024 02:41:41 GMT

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!

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Tue, 14 Apr 2020 14:59:58 GMT

Can anyone help me here?

If more information is needed, please let me know!

Thanks!

Re: Sum in straight table is not matching with data

lorenzoconforti — Tue, 14 Apr 2020 15:22:32 GMT

can you post a sample dashboard? It would be easier to help you

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Tue, 14 Apr 2020 15:59:39 GMT

Hi Lorenzo,

Unfortunately, I cannot provide a data sample, as long as i'm working on PRD Data...

Perhaps, a print screen of the KPI with the value and a sample of the csv data is enough?

Thanks!

Re: Sum in straight table is not matching with data

lorenzoconforti — Tue, 14 Apr 2020 16:08:04 GMT

ok, let's start with that

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Tue, 14 Apr 2020 16:59:13 GMT

Here you go!

-Print Screen: Values showed in the dashboard

-Excel file: Exported data from the dashboard

Let me know if this suits you or not!

Re: Sum in straight table is not matching with data

lorenzoconforti — Tue, 14 Apr 2020 17:04:58 GMT

What measure are you using in the pivot? Also, can you post a screenshot of the straight table as well?

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Wed, 15 Apr 2020 08:20:16 GMT

The Measure is pretty much the same:

Measure:

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

Thanks for the help!

Re: Sum in straight table is not matching with data

lorenzoconforti — Wed, 15 Apr 2020 08:25:32 GMT

can you check two things:

- if you remove set analysis, do totals match?

if(Valuelist('ExampleA') = 'ExampleA', Sum([Interactions]))

- if you remove valuelist, do totals match?

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

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Wed, 15 Apr 2020 09:56:28 GMT

Tried both approaches: None of them made the values to change/match.

(On the approach to remove the set analysis, i've made the selections by 'hand', also)

Re: Sum in straight table is not matching with data

lorenzoconforti — Wed, 15 Apr 2020 13:28:28 GMT

It might be an aggregation problem; try this:

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

Re: Sum in straight table is not matching with data

50ShadesOfSalty — Wed, 22 Apr 2020 07:35:42 GMT

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!

Re: Sum in straight table is not matching with data

lorenzoconforti — Wed, 22 Apr 2020 07:46:25 GMT

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