CODEWITHSUNDEEP
powerbidax
Niyas
Niyas

Reputation: 515

GroupBy and aggregate in DAX

I'm new to DAX queries and trying to create a DAX query equivalent to following SQL query. 

SELECT Year, Company, SUM(Actual Sales) as Actual, SUM(Planned Sales) as Planned
FROM Sales
GROUP BY Year, Company

I'm not able to take aggregate of multiple columns after groupby in DAX. How can I resolve this?

Upvotes: 0

Views: 5842

Answers (1)

Joe G
Joe G

Reputation: 1776

There are actually a few ways in Power BI to do this kind of aggregation. Here are three different options with slightly different results from each other.

Option 1: Query Editor

In the query editor window, select Transform -> Group By...

Group by

... and configure it as shown below (make sure to click the Advanced option).

Group by config

This results in the below. Note - this replaces the starting table.

Group by results

Option 2: Measures

In the regular window (not the query editor used in option 1), click on Modeling -> New Measure...

New measure

... to create two measures using the formulas below.

Actual = SUM(Sales[Actual Sales])

Planned = SUM(Sales[Planned Sales])

Once those measures are created, a visual (a matrix in this case) can be created with the desired headers and values.

Measure matrix

Option 3: Columns

Similarly to option 2, click on Modeling -> New Column...

New columns

... to create two columns using the formulas below.

Actual = CALCULATE(
    SUM(Sales[Actual Sales]),
    FILTER(
        Sales,
        Sales[Year] = EARLIER(Sales[Year]) &&
        Sales[Company] = EARLIER(Sales[Company])
    )
)

Planned = CALCULATE(
    SUM(Sales[Planned Sales]),
    FILTER(
        Sales,
        Sales[Year] = EARLIER(Sales[Year]) &&
        Sales[Company] = EARLIER(Sales[Company])
    )
)

Now the aggregations are in columns that can be used report visuals.

Column results

Upvotes: 9

Related Questions