Working with X-Functions in DAX (SUMX, AVERAGEX, etc.)

[Ilgar_Zarbali]

Our sales data is in Azerbaijani Manat, but we also want to display it in USD. To achieve this, we add a calculated column to the fSales table:

Sales in USD = fSales[Amount] / RELATED('DCurrency(F)'[EUR/USD])

Now, we can visualize sales data in both Euros and USD by store. However, using X-functions (iterators) allows us to perform the same calculation virtually, eliminating the need for calculated columns.

Let’s replicate the functionality of the calculated column using the SUMX function:

Sales USD = SUMX(fSales, fSales[Amount] / RELATED('DCurrency(F)'[EUR/USD]))

 Adding this measure to a table yields the same results as the calculated column. Once confirmed, we can delete the calculated column.

Here’s another example: calculating the average check amount. In the fSales table, there’s a column for cash slips. Each cash slip can have multiple rows, so taking a simple average of the Amount column won’t work. First, we need to aggregate the table by cash slip number, then calculate the average.

Using the SUMMARIZE function, we can create an aggregated table:

Average Slip= SUMMARIZE(fSales, fSales[Cash Slip Number])

This gives us a list of unique cash slip numbers. To calculate the total amount for each slip, we can use the ADDCOLUMNS function:

Table = ADDCOLUMNS(
    SUMMARIZE(fSales, fSales[Cash Slip Number]),
    "Slip Amount",
    CALCULATE(SUM(fSales[Amount]))
)

We can now use this table to display the data. Replacing SUM with AVERAGE will give the average check amount. However, since this table is not dynamically linked to the model, it has some limitations. The results are static and cannot adapt to slicers or filters.

To make this dynamic, we use the AVERAGEX function:

Average Slip = AVERAGEX(
    ADDCOLUMNS(
        SUMMARIZE(fSales, fSales[Cash Slip Number]),
        "Slip Amount",
        CALCULATE(SUM(fSales[Amount]))
    ),
    [Slip Amount]
)

A Simpler Approach:

Another way to calculate the average check is by dividing the total sales amount by the distinct number of slips:

Average Slip Simple = [TotalSales(F)] / DISTINCTCOUNT(fSales[Cash Slip Number])

This method is more efficient and faster than the previous one, emphasizing the importance of understanding the requirements of each task before choosing the approach.

Conclusion X-Functions like SUMX, AVERAGEX, and others are indispensable tools for dynamic calculations in Power BI. They allow you to replace static calculated columns with more efficient, flexible measures, enhancing both performance and adaptability. Whether you're converting sales to different currencies or calculating averages across grouped data, mastering these functions is essential for any data professional. Let us know if you found these examples and tips helpful—we’d love to hear your feedback!