Forum Discussion
totals dont match
- 1 year ago
i removed the one FYTD table from the model. I converted floats to decimal (18,6). I moved some calc responsibility away from DAX into the sql views. I left some applied sort , rename and maybe dupe steps deleted. I removed one critical flag that was probably working but now cant even get to pbi thanks to a sql predicate blocking it.
All numbers match now not only to our warehouse $'s, but between the desktop and service as well. Hard to explain (rounding errors) to the user when the main focus was on making the report smarter on the 1st of the month. The report always tried to report thru "Yesterday". With previous authors so fixed on the Today() function, that created some challenges.
i didnt play with the float to decimal conversion recommendation YET, but i did scour everything for possible join issues using ssms as a guage. And dont see anything wrong but need to understand cross filtering a bit to rule that out.
an old report in SSRS nailed the totals almost to the penny compared with simulated (what i think pbi should be doing) t-sql with the same underlying float values. And the simulated t-sql reconciled to the penny against simple sums over the core fact table without possible interference from joins.
can the community tell me if the following model looks like one of those situations where something passing thru from one table is affecting results on the other side or overall? I forget what its called and admittedly never read a good explantion. I think it has to do with cross filtering. The cross filter direction on each relationship is single. The Dates table is marked as a date table. I can probably get rid of the FYTD Date table by blending it with the other date table (i inherited this whole thing). And i may transfer some responsibility away from dax and into t-sql to get this under control.
the aggregations in here summarize to maybe 24.5k records (originating in 2-3 million) so i find it diffficult to believe that rounding is going to explain this but i'll convert to decimal anyway to level the playing field.
i removed the one FYTD table from the model. I converted floats to decimal (18,6). I moved some calc responsibility away from DAX into the sql views. I left some applied sort , rename and maybe dupe steps deleted. I removed one critical flag that was probably working but now cant even get to pbi thanks to a sql predicate blocking it.
All numbers match now not only to our warehouse $'s, but between the desktop and service as well. Hard to explain (rounding errors) to the user when the main focus was on making the report smarter on the 1st of the month. The report always tried to report thru "Yesterday". With previous authors so fixed on the Today() function, that created some challenges.
- v-karpurapud1 year ago
Community Support
Hi db042190
Thank you for the detailed updates and for sharing your findings. Could you please confirm whether the original issue with the FYTD totals has now been fully resolved?
Regards,
Karpurapu D.