How can I Use DAX Functions (INDEX, OFFSET, and WINDOW) for Powerful Power BI Visual Table Calculations?š
Create the table showing the total orders on the basis of region. Letās add an INDEX to the table. You can use the field āSales Territory Keyā for this.
If we want to calculate the orders for Germany. Letās start with the INDEX function. First of all, create a new measure.
Secondly, call the total order, go to the filter section, and then write the INDEX function.
INDEX parameter retrieves a row at an absolute (specified by the position of the parameter) within the specified partition sorted by the specified order or on the axis specified.
INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>])
Calculate the orders for Germany, and write 8 in the index, so there are 8 rows in the visible Table. Go to the second parameter, now letās specify the relationship with the visible table, 8 rows indicate the 8 rows in the whole table. Therefore, call the ALL function and type the visible table. (If we want to specify the order from bottom to top, we can do this by writing a negative number in the position.š)
If you want the index to be in the order you want, then you can use the ORDERBY parameter of the function. (Sort by the Region names in the table.) For example, to calculate the order for Canada, you can write ā2ā in the index.
INDEX =
CALCULATE(
[Total Orders],
INDEX(2,ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Region])))
We use INDEX for comparison purposes in analysis.
Letās see what the OFFSET function does. OFFSET function returns a single row that is positioned either before or after the current row within the same table, by a given offset. Multiple rows may be returned if the current row cannot be deduced to a single row.
OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )
Create a new measure. The name of the Total Order OFFSET. Letās calculate the order that precedes it for each row in the visual table for this, type the position parameter ā-1ā, specify the related table, call the retrieve function, and type the āSales Territory Keyā, followed by the āRegionā.
OFFSET =
CALCULATE(
[Total Orders], OFFSET(-1, ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]), ORDERBY(AW_Territories_Lookup[Region],ASC)))
OFFSET and INDEX functions return a single row. However, the WINDOW function returns the area to the specified line spacing again. Letās create a new measure.
The WINDOW function returns multiple rows which are positioned within the given interval.
WINDOW ( from[, from_type], to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )
Remember that every line is in the zeroth position before the range starts. For each line and If we want to sum in the preceding line, make the beginning -1 and the end 0.
WINDOW =
CALCULATE(
[Total Orders],
WINDOW(-1,REL,0,REL,ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Region])))
You can add a slicer to your report and change the position number. So you have the flexibility to choose any range from the slicer.
For example, if you do the position ā-10ā in this example you can get a cumulative total.
For example, you can calculate the total order only for Australia and Canada by sorting measures to make the position 1 ABS and 2 ABS.
For example, you can write the grand total order in each row in the table.
If you want to calculate the cumulative total, the relative value from the absolute first position that is, you need to add up to each line.
Letās look at the other parameter of the WINDOW function, the PARTITIONBY parameter. If we want a continent-specific total, we need to divide the visible table first. PARTITIONBY defines the columns used to partition a window functionās <relation> parameter.ā
WINDOW_PARTITIONBY =
CALCULATE(
[Total Orders], WINDOW(1, ABS,-1,ABS, ALL(AW_Territories_Lookup[Continent],AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Continent]), ,PARTITIONBY(AW_Territories_Lookup[Continent])))
š Donāt forget to subscribe to our Power BI publication and Weekly Newsletterā¦
I hope this post helps! Let me know if you have any questions.
Hope to see you on the next storyā¦
Check out my other articles:
šDirect Lake in Microsoft Fabric: A Revolutionary Feature for Data Storage and Analysis
š£Microsoft Fabric: Simplifying Complexity in the Data Analytics Process
šHow to Create an Organizational Hierarchy in Power BI?š„