Total Calculation with multiple fact Table

[ignas]

I cannot make changes in Data Model, so I hope there is a possibility to calculate correctly totals in measure.

Data model:

It is a simplified version of a real model.

Correct Absolute Turnover should be 15

I cannot get the correct "Absolute Turnover Target_Control" measure.

Absolute Turnover Target_Control HASONEFILTER = 
        IF(
            HASONEFILTER(dimName[Name]);
            [Target+control Size]*[AVG Target turnover];
            "need help"
            )

AVG Target turnover = DIVIDE([Turnover Target];SUM(factMetrics[TargetSize]))

Target+control Size = SUM(factGroup[GroupSize])+SUM(factMetrics[TargetSize])

Turnover Target = CALCULATE(SUM(factTurnover[Turnover]);factTurnover[Group]="Target")

I tried different options, but none of them worked.
@ImkeF  The last time you helped me with similar issue maybe you will know also this time?

The file can be found here: File 

[amitchandak]

@ignas ,

Multiplication needs to be sum(A*B). As we already have aggregated columns, We need to get the correct row context. You need to change the group by in summarize to force calculation at the correct row level

I added three of them

sumx(summarize(Table,Table[Name],table[Month], table[Segment],"_1",[Target+control Size],"_2",[AVG Target turnover]),[_1]*[_2])

[ignas]

@amitchandak Thanks a lot for such a quick response. I am not entirely sure how I can implement your suggested solution. There is no one table that I can summarise. Measure come from 3 different tables: factMetrics, factTurnover and factGroup.

How can I use summarise function in this case?

[amitchandak]

You can summarize across common dimensions.

or Use one fact in summarizing and common dimesions and the measure from other tables.

[ignas]

@amitchandak I do not know how I can summarise across the common dimensions.  Could you please help me with the formula?
Summarise function is used for one Table as far as I know.
I cannot use one fact in summarising, because in the real data model none of the facts contain a full list.

The file can be found here: File 

[amitchandak · Accepted solution]

@ignas 

I created it like this.

Absolute Turnover Target_Control = 
sumx(SUMMARIZE(factGroup,dimName[Name],dimMonth[Month],dimSegment[Segment],"_1",[Target+control Size],"_2",[AVG Target turnover]),[_1]*[_2])

and corrected datatype.  it coming 14.48. Can you explain how it should be 15.

File attached.

[ignas]

@amitchandak You saved my day. It was a bit too difficult for me, but now I have learned. Highly appreciated.
By the way, how are you able to attach the file?

I cannot find this button.

[amitchandak]

I am a superuser. Superuser, superpower 😀