MSPTDA 24: DAX Multiply / Divide Data Types Discrepancies Solved!

[@ljubicar1987]

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!

[@GeertDelmulle]

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!

[@excelisfun]

I like: Decimal all the way, until Measure : ) You are welcome, Geert!

[@GeertDelmulle]

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.

[@excelisfun]

@@GeertDelmulle , wow!!! That really is cool : ) Power Query can do anything! Thanks for the trick, Geert!!!!

[@katerina6495]

Thank you Mike, great video as always, have a great day

[@excelisfun]

You are welcome! It has been a good day! I hope you day is good too, Katerina : )

[@DIGITAL_COOKING]

Awesome! Mike, great video, and I agree with Greet Delmulle about the usage of currency

[@excelisfun]

Me too : ) Go Team!!!!!

[@SyedMuzammilMahasanShahi]

This is so awesome. Thanks Mike for this amazing video.

[@excelisfun]

You are welcome, Syed!!!!!

[@joelngige5776]

Nice insights into how data types affects calculations. Thanks

[@excelisfun]

Glad it helps. Just use Decimal lol

[@MalinaC]

Wow, it is so important to use proper data type, great exaple. Thanks, Mike!

[@excelisfun]

You are welcome, Malina!!!

[@wayneedmondson1065]

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!!

[@shubhampawar8506]

Very minute detail ..thanks a lot ❤️

[@wayneedmondson1065]

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!

[@excelisfun3903]

It is a booby trap...

[@ismailismaili0071]

thank you so so much for letting us pay attention to this issue.

[@excelisfun]

You are welcome, Ismail!!!!

[@rrrprogram8667]

Thanks for the tips mike

[@excelisfun]

You are welcome for the tip, RRR!

[@chrism9037]

Thanks Mike!

[@excelisfun]

You are welcome, Chris Mancinelli!!!!

[@ogwalfrancis]

Understanding decimal, Thanks Mike

[@excelisfun]

You are welcome, Ogwal!!!!

[@masterof]

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?

[@masterof]

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...

[@excelisfun]

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.

[@excelisfun]

@@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.

[@jeffkasavan93]

Mike, thanks for the information. You make a valid "(decimal) point". :) :)

[@excelisfun]

Niiiiiiiice!!!!

[@danjarupath]

Awesome 👍👍👍👍😎😎😎😎

[@excelisfun]

Important oddity to know for sure : )

[@ndjanardhan]

Thanks you sir, for great tips about decimal

[@excelisfun]

You are welcome, Janardhan!!!!

[@johnborg6005]

That was great and interesting. Thanks Mike :)

[@excelisfun]

You are welcome for the interesting, John Borg!!!

[@sumardjo]

thanks information very clear thanks

[@excelisfun]

You are welcome, Rusdi!!! Thanks for your consistent support : ) : )

[@HachiAdachi]

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...

[@excelisfun]

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...

[@davidpellatt3501]

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.

[@excelisfun]

Thanks for the tip, David!

[@shubhampawar8506]

I will keep it all decimal

[@excelisfun]

Not in Excel worksheet or Power Query, but DAX...

[@shubhampawar8506]

@@excelisfun Yes 😊

[@stevennye5075]

well done!

[@excelisfun]

So much different than Number Formatting in Excel, right Steven Nye?

[@davebowman5392]

Thanks Mike

[@excelisfun]

You are welcome, Dave Bowman!!!!

[@mohamedchakroun4973]

In this situation i stocks all data as a decimal :-)

[@excelisfun]

I agree : )

[@simfinso858]

If I am making payments I will use Decimal & If I am Receiving money i will use currency.

[@hosseinhosseinpoor9561]

top

[@gentle2005phir]

Hope CDW will be >= WDC always, due to rounding up in C

[@excelisfun]

I think that is correct, Gentle Raj!

[@joaquimcosta952]

Uau.......

[@excelisfun]

Right!!!!