MSPTDA 24: DAX Multiply / Divide Data Types Discrepancies Solved!
Вставка
- Опубліковано 1 жов 2024
- Download files:
Start File: people.highlin...
Finished File: people.highlin...
pdf notes for Video #24: people.highlin...
In this video learn about how different DAX Data Types can affect multiplication and division results and yield different answers depending on the order of the multiplication or division. This apparently violates the Commutative Property of Multiplication.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Dang, until 1 minute ago I used to be “precise” in my data type assigning.
From now on it’s ‘decimal’ all the way until I reach the measure level.
BTW: this remark only applies in the “currency vs. decimal” department.
Thanks, Mike!
I like: Decimal all the way, until Measure : ) You are welcome, Geert!
BTW Mike, today I refined your reading ‘all files from folder’-trick by adding a Keyword-variable that I use to select only certain files (the ones where the file names contain the Keyword). That way I can restrict the number of files that are read/used in a workbook. Conversely, I don’t have to put all possible input files in separate folders for the workbook to work correctly.
I find that a nice PQ-EMT. Maybe you can use that in one of your videos.
@@GeertDelmulle , wow!!! That really is cool : ) Power Query can do anything! Thanks for the trick, Geert!!!!
Thank you Mike - this one is important. I didn't know that will cause the results to be calculated differently. What is the benefit of using the Currency data type? In order to keep all the calculation precisely, should I use Decimal data type in all columns with numbers?
On the other hand, I remember I have seen it somewhere, but struggled to understand this: When I make a pivot table from the data model, the column total in the pivot table doesn't seem to be calculated correctly. I had to switch off the total in the pivot table and do a formula line to calculate the total instead. How to prevent/avoid this situation? Manually build a total line for pivot table isn't difficult, but it gets tricky in a pivot chart. Thank you Mike...
Yes, I think Decimal most of the time to keep precision is good and then use the formatting on the Measures or in the reports at the end. Unless the business calculation requires rounding at some points, like money often does for invoices, payroll and other, then we should manually round.
@@masterof There are 100s of reasons that the totals in a DAX Data Model PivotTable could be incorrect. We have to analyze that one situation at a time. It might be becasue of different Data Types and math, but it could be many other reasons also.
Thank you Mike, great video as always, have a great day
You are welcome! It has been a good day! I hope you day is good too, Katerina : )
Thanks Mike!
You are welcome, Chris Mancinelli!!!!
Awesome! Mike, great video, and I agree with Greet Delmulle about the usage of currency
Me too : ) Go Team!!!!!
Thanks for the tips mike
You are welcome for the tip, RRR!
If I am making payments I will use Decimal & If I am Receiving money i will use currency.
Awesome 👍👍👍👍😎😎😎😎
Important oddity to know for sure : )
Very minute detail ..thanks a lot ❤️
This is so awesome. Thanks Mike for this amazing video.
You are welcome, Syed!!!!!
I have been under the notion that the use of currency data type would result in reduction in data size, so I have been quick to use the data type especially when working with a large dataset (usually in PQ). Now you make me wonder if the benefit of smaller data size (and maybe some performance improvement) is worth the cost of potential inaccuracy...
It all comes down to the precision of the calculation. Your idea of using Currency to reduce size, can reduce size, and if all the columns require up to 4 decimals, then that is fine! I like to manually round (ROUND or Number.Round) in accordance with the rules for each calculation...
Hi Mike, To be honest I nearly always upload my data to Power Query and set the data types there before adding the data to the data Model. I then set the data type only within my DAX Measures. Regards.
Thanks for the tip, David!
Nice insights into how data types affects calculations. Thanks
Glad it helps. Just use Decimal lol
I will keep it all decimal
Not in Excel worksheet or Power Query, but DAX...
@@excelisfun Yes 😊
top
thanks information
very clear
thanks
You are welcome, Rusdi!!! Thanks for your consistent support : ) : )
Hi Mike.. GREAT video on this topic. Super solutions.. triple the fun! Going to get some more popcorn and watch this one again! Thumbs up!!
The decimal is sure the safest option! I wouldn't guess the data type could be a reason for the difference in a million years! Thanks for sharing!
Wow, it is so important to use proper data type, great exaple. Thanks, Mike!
You are welcome, Malina!!!
well done!
So much different than Number Formatting in Excel, right Steven Nye?
Hi Mike.. thanks for the great video and exposing the issue of data type and order of use in calculation. Kind of a booby trap hiding in plain sight. Thanks for sharing your wisdom. Thumbs up!
It is a booby trap...
Mike, thanks for the information. You make a valid "(decimal) point". :) :)
Niiiiiiiice!!!!
Uau.......
Right!!!!
Thanks Mike
You are welcome, Dave Bowman!!!!
Hope CDW will be >= WDC always, due to rounding up in C
I think that is correct, Gentle Raj!
Thanks you sir, for great tips about decimal
You are welcome, Janardhan!!!!
Understanding decimal, Thanks Mike
You are welcome, Ogwal!!!!
thank you so so much for letting us pay attention to this issue.
You are welcome, Ismail!!!!
That was great and interesting. Thanks Mike :)
You are welcome for the interesting, John Borg!!!
In this situation i stocks all data as a decimal :-)
I agree : )