PARTITIONBY
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
The PARTITIONBY function is a very useful DAX function that allows you to partition your data into groups based on one or more columns. This function is particularly useful when you need to perform calculations on subsets of data within a larger dataset.
In this article, we will explore how to use the Power BI DAX function PARTITIONBY. We will provide examples and step-by-step instructions to help you get started.
What is the PARTITIONBY Function?
The PARTITIONBY function is a DAX function that allows you to group data based on one or more columns. It works similarly to the GROUPBY function, but with some important differences.
The PARTITIONBY function creates partitions or subsets of data based on the values in one or more columns. These partitions can then be used to perform calculations on the data within each partition. The result of the calculation is then returned as a new table.
Syntax of the PARTITIONBY Function
The syntax of the PARTITIONBY function is as follows:
PARTITIONBY(Column1[, Column2[, ...]])
Where Column1 is the column that you want to partition the data by. You can include additional columns to create more partitions.
How to Use the PARTITIONBY Function in Power BI
To use the PARTITIONBY function in Power BI, follow these steps:
1. Open Power BI Desktop and create a new report.
2. Import your data into Power BI.
3. Select the table that you want to use the PARTITIONBY function on.
4. Click on the “New Measure” button on the “Modeling” tab.
5. In the formula bar, type in the following formula:
MeasureName = CALCULATE(YourCalculation, PARTITIONBY(Column1[, Column2[, ...]]))
Where MeasureName is the name of your new measure, YourCalculation is the calculation that you want to perform, and Column1[, Column2[, …]] is the column or columns that you want to partition the data by.
6. Click on the “Check” button to validate your formula.
7. Click on the “Save” button to save your new measure.
Example of Using the PARTITIONBY Function in Power BI
Let’s say that you have a table of sales data that includes the following columns:
– Date
– Product
– Sales
You want to calculate the total sales for each product, but you also want to show the percentage of sales each product contributed to the total sales. You can use the PARTITIONBY function to accomplish this.
1. Open Power BI Desktop and create a new report.
2. Import your sales data into Power BI.
3. Select the sales table.
4. Click on the “New Measure” button on the “Modeling” tab.
5. In the formula bar, type in the following formula:
Total Sales = SUM(Sales[Sales])
6. Click on the “Check” button to validate your formula.
7. Click on the “Save” button to save your new measure.
Next, create a new measure for the percentage of sales each product contributed to the total sales.
Percentage of Sales =
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(SUM(Sales[Sales]), PARTITIONBY(Sales[Product]))
)
In this formula, we are using the DIVIDE function to divide the total sales by the sales for each product. We are then using the CALCULATE function with the PARTITIONBY function to create partitions based on the product column.
The PARTITIONBY function is a powerful tool in Power BI that allows you to partition your data into subsets for more targeted calculations. By using this function, you can perform complex calculations on subsets of your data with ease.
In this article, we have provided an overview of the PARTITIONBY function and how to use it in Power BI. We have also provided an example to help you get started with using this function in your own reports.
