Blog

You are currently viewing Number functions in PowerBI

Number functions in PowerBI

Loading

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

5001.PNG

Now, let’s dive into different Number functions:

1- Arithmetic Functions

ADD:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6003.PNG

SUBTRACT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6004.PNG

MULTIPLY:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6005.PNG

DIVIDE:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6006.PNG

2- Aggregation Functions

SUM:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6007.PNG

AVERAGE:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6008.PNG

MEDIAN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6009.PNG

MIN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6010.PNG

MAX:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6011.PNG

COUNT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6012.PNG

COUNTA:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6013.PNG

COUNTAX:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6014.PNG

COUNTX:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6015.PNG

COUNTROWS:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6016.PNG

DISTINCTCOUNT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6017.PNG

3- Rounding Functions

ROUND:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6018.PNG

ROUNDDOWN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6019.PNG

ROUNDUP:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6020.PNG

MROUND:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6021.PNG

TRUNC:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6022.PNG

INT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6023.PNG

CEILING:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6024.PNG

FLOOR:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6025.PNG

4- Statistical Functions

STDEV.P:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6026.PNG

STDEV.S:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6027.PNG

VAR.P:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6028.PNG

VAR.S:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6029.PNG

PERCENTILE.EXC:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6030.PNG

PERCENTILE.INC:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6031.PNG

4- Logarithmic and Exponential Functions

LOG:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6032.PNG

LOG10:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6033.PNG

EXP:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6034.PNG

LN:

Step 1. Go to Table view

6001.PNG

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 ✔:

6037.PNG

TAN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6038.PNG

ASIN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6039.PNG

ACOS:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6040.PNG

ATAN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6041.PNG

6- Miscellaneous Functions

ABS:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6042.PNG

SIGN:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6043.PNG

MOD:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6044.PNG

PI:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6045.PNG

POWER:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6046.PNG

SQRT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6047.PNG

7- Conversion Functions

VALUE:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6048.PNG

FORMAT:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6049.PNG

8- Information Functions

ISNUMBER:

Step 1. Go to Table view

6001.PNG

Step 2. Now go to Table tools, select New Column

6002.PNG

Step 3. Type the following in formula bar and Click on ✔:

6050.PNG

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.