Calculation on lowest level of the hierarchies and summing the results on aggregated levels

[Sander76]

Dear All, I have an issue with getting the correct result on aggregated levels. I have a model with three  factables  (all related to the same dim tables and also good to now it is in direct query mode);

Cost Per unit:

 Bill of Materials

 And a third one where I create the result, in the model this table is actualy empty and filled with a measure;

Where MeasureCost is the following

MeasureCost = SUMX('OLAP CUBE_Cost per Unit_WB','OLAP CUBE_Cost per Unit_WB'[Amount])*SUMX('OLAP CUBE_BOM_WB','OLAP CUBE_BOM_WB'[Amount])

If I do this I get the following result, which is correct on lowest level, but not on agggregations;

On aggregation actualy the calculation is performed instead of summing up the result .

Normally I would solve this with calculated columns but in this case this isn't possible becasue it is in direct query mode and I cannot use the aggregation function becasue they are not supported in direc query mode.

Direct query mode is mandatory in this case.

Can anyone help me out here?

KR, Sander

[Sander76 · Accepted solution]

I found the solution. First calculated the measure, and then used another measure with sumx to also get it OK on aggregated level;

Amount1 = CALCULATE(SUM(Sheet1[ Price])*SUM(Sheet1[Quantity]))

Amount2 = SUMX(Sheet1,[Amount1])

[Sander76]

Found the solution, will post the pbix also

[Sander76]

Will also post a simplified model

[Sander76]

I think I'm almost there, formala need to besomething like

MeasureCost = if(HASONEVALUE('OLAP Cost Type'[ID]),SUMX('OLAP CUBE_Cost per Unit_WB','OLAP CUBE_Cost per Unit_WB'[Amount])*SUMX('OLAP CUBE_BOM_WB','OLAP CUBE_BOM_WB'[Amount]),[Some Formula that aggregates the lower levels instead of performing the calc])

[v-easonf-msft]

Hi, @Sander76 

Can you decompressed your real-life problem into a simple case?

If you can  provide a sample pbix for testing,it will helop us understand and deal with this problem.

Best Regards,
Community Support Team _ Eason