10 Replies Latest reply: Jan 26, 2018 6:56 AM by pradosh thakur

# Problem with Group By?

Hi,

I have a fact table and my goal is to do following calculation:

(23833,41 / 36) * 9

What I am having issues getting into the Fact Table is 36. 36 is a sum of the rows in "DagMatriseVerdi2018%".

I've tried to do a group by on %ProposalLine_%UkeID_Key and join back in but I get a really large number as you can see.

LEFT JOIN (Temp_Fakta_2)

[%PropsalLine_%UkeID_Key],

Sum(DagMatriseVerdi2018) AS UkeMatriseVerdi2018,

Sum(DagMatriseVerdi2018%) AS UkeMatriseVerdi2018%

Resident Temp_Fakta_2

Group By %PropsalLine_%UkeID_Key;

Any help is appreciated.

Best,

Ali

• ###### Re: Problem with Group By?

i guess your question is not clear.

could you please post a sample app with mock data and show there your expected result?

• ###### Re: Problem with Group By?

There you can see in the correct column what I want it to be.

• ###### Re: Problem with Group By?

does this help on front end

([your last column]/sum(total <%ProposalLine_%UkeID_Key>DagMatriseVerdi2018%)) * %ProposalLine_%UkeID_Key

• ###### Re: Problem with Group By?

Well - in the table you have a screenshot of this expression should do the sum you're asking for:

Sum(TOTAL [DagMatriseVerdi2018%])

Add an extra column and use the above expression just to verify that it calculates to 36 on all the rows.

If it does not return 36 on all rows it might be a "Sum of Expression" that does not calculate to the same of "Sum of Rows". Then the solution is often using the fantastic advanced aggregation function Aggr(...):

Sum( Aggr( Sum([DagMatriseVerdi2018%]) , %ProposalLine_%UkeID_Key ) )

• ###### Re: Problem with Group By?

What it says in in the "Correct" column is what I am trying to create in the fact table on a date level.

• ###### Re: Problem with Group By?

Sorry - answering a bit quickly in the last response not noticing all the dimensions in the chart...

What does this calculate to in a measure in your chart?

Sum( TOTAL

<%ProposalLine_%UkeID_Key,Uke,DagMatriseVerdi2018%,UkeMatriseVerdi2018%>

[DagMatriseVerdi2018%]

)

• ###### Re: Problem with Group By?

This also gives me 2268

• ###### Re: Problem with Group By?

I guess you have more than one year then?

• ###### Re: Problem with Group By?

Did you say 2268 and really meant that it gives 8568?

• ###### Re: Problem with Group By?

try this

sum(total <%ProposalLine_%UkeID_Key>DagMatriseVerdi2018%)