Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 ,
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.
@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
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.
@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.
I am a superuser. Superuser, superpower 😀
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |