SUMMARIZECOLUMNS

How to Use the Power BI DAX function SUMMARIZECOLUMNS

What is SUMMARIZECOLUMNS?

SUMMARIZECOLUMNS is a DAX function that allows you to create a summary table based on one or more columns from the underlying data. The summary table can be used to show aggregated values such as totals, averages, minimum and maximum values, and counts.

Syntax of SUMMARIZECOLUMNS

The syntax of SUMMARIZECOLUMNS is as follows:

SUMMARIZECOLUMNS(column1, [column2], [column3],…, [expression1], [expression2],…)

Where:

– column1, column2, column3, … are the names of the columns that you want to group by.

– expression1, expression2, … are the expressions that you want to evaluate for each group.

How to Use SUMMARIZECOLUMNS in Power BI

To use SUMMARIZECOLUMNS in Power BI, follow these steps:

1. Open Power BI Desktop and load your data into the model.

2. Create a new measure or expression that you want to summarize.

3. Create a new table visualization and drag the columns that you want to group by into the Rows field.

4. Click on the New Measure button and enter the following DAX expression:

SUMMARIZECOLUMNS(column1, [column2], [column3], …, [expression1], [expression2], …)

5. Replace column1, column2, column3, … with the names of the columns that you want to group by.

6. Replace expression1, expression2, … with the expressions that you want to evaluate for each group.

7. Click on OK to create the measure.

8. Drag the measure into the Values field of the table visualization.

9. You should now see a summary table that shows the aggregated values for each group.

Examples of Using SUMMARIZECOLUMNS

Here are some examples of how you can use SUMMARIZECOLUMNS in Power BI.

Example 1: Summarizing Sales by Product and Region

Suppose you have a sales data table that contains the following columns:

– ProductName

– Region

– SalesAmount

To summarize the sales data by product and region, you can use the following expression:


SUMMARIZECOLUMNS(

Sales[ProductName],

Sales[Region],

“Total Sales”, SUM(Sales[SalesAmount])

)


This expression groups the sales data by ProductName and Region columns and calculates the total sales for each group.

Example 2: Summarizing Sales by Year and Month

Suppose you have a sales data table that contains the following columns:

- OrderDate

- SalesAmount

To summarize the sales data by year and month, you can use the following expression:


SUMMARIZECOLUMNS(

YEAR(Sales[OrderDate]),

MONTH(Sales[OrderDate]),

“Total Sales”, SUM(Sales[SalesAmount])

)


This expression groups the sales data by year and month columns and calculates the total sales for each group.

SUMMARIZECOLUMNS is a powerful DAX function in Power BI that allows you to create summary tables based on one or more columns from the underlying data. By using this function, you can easily summarize and analyze your data in different ways and gain valuable insights into your business.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)