Forum Discussion
Total Calculation with multiple fact Table
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
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.
7 Replies
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])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?
You can summarize across common dimensions.
or Use one fact in summarizing and common dimesions and the measure from other tables.
