In this blog, we will implement different Number functions in PowerBI such as Arithmetic functions, Aggregation function, Round functions and many more. Then we can get the required and importance of following functions:
Introduction to Number functions in PowerBI
Number functions in PowerBI provide tools to perform calculations and transformations on numeric data, enabling users to round, format, or manipulate numbers. Common functions include ROUND, INT, ABS, TRUNC, and FORMAT, allowing precise control over numeric values for creating calculated columns, measures, and visualizations in data analysis and reporting.
Dataset Description
This financial dataset details sales metrics, including sales amounts, taxes, total sales, costs, pricing, quantities, orders, and additional values, facilitating in-depth analysis of revenue, costs, and order patterns. You can download the dataset from here.
Steps to implement Number functions in PowerBI
Step 1. Open Power BI Desktop
Step 2. Click on Get Data to select Text/CSV in Home tab
Step 3. Browse csv file and hit Open
Step 4. A window will open to Transform Data and Load, click on Load
Now, let’s dive into different Number functions:
1- Arithmetic Functions
ADD:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
SUBTRACT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MULTIPLY:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
DIVIDE:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
2- Aggregation Functions
SUM:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
AVERAGE:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MEDIAN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MIN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MAX:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COUNT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COUNTA:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COUNTAX:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COUNTX:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COUNTROWS:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
DISTINCTCOUNT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
3- Rounding Functions
ROUND:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
ROUNDDOWN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
ROUNDUP:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MROUND:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
TRUNC:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
INT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
CEILING:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
FLOOR:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
4- Statistical Functions
STDEV.P:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
STDEV.S:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
VAR.P:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
VAR.S:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
PERCENTILE.EXC:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
PERCENTILE.INC:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
4- Logarithmic and Exponential Functions
LOG:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
LOG10:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
EXP:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
LN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
5- Trigonometric Functions
SIN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
COS:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
TAN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
ASIN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
ACOS:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
ATAN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
6- Miscellaneous Functions
ABS:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
SIGN:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
MOD:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
PI:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
POWER:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
SQRT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
7- Conversion Functions
VALUE:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
FORMAT:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
8- Information Functions
ISNUMBER:
Step 1. Go to Table view
Step 2. Now go to Table tools, select New Column
Step 3. Type the following in formula bar and Click on ✔:
Conclusion:
Number functions play important role in data analytics for performing variety of mathematical, statistical operation on data. By using it, an analyst can find meaningful insights, pattern and make decisions with it.
With this documentation, now we have an idea that how can we perform above type of function on a dataset.
If you like this blog, you can share it with your friends or colleague. You can connect with me on social media profiles like LinkedIn, Twitter, and Instagram. If you have any doubt feel free to comment.
- 👏 Like for this article and subscribe to our newsletter
- 📰 View more content on my DataSpoof website
- 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
