Group and aggregate by calculated column

[csaba09]

Hi All,

I'd like to write a DAX query that returns a table containing an aggregation grouped by a physical column AND a calculated column.

I have the below tables in the model:

fact_sales:

product_id	country_id	sales
1	11	12
2	22	4
3	11	7
4	11	5
5	22	9

dim_product:

product_id	product_type
1	fruit
2	fruit
3	legume
4	vegetable
5	legume

dim_country:

country_id	country_name
11	Spain
22	Portugal

I'd like to sum sales and group by country name and whether the product is 'fruit' or 'not fruit', returning the below:

country_name	fruit_or_not	sales
Spain	fruit	12
Spain	not_fruit	12
Portugal	fruit	4
Portugal	not_fruit	9

So the key here is I want to create a calculated 'fruit_or_not' column in the query and use that for grouping later in the same query.

How would I go about this?

I tried creating a table variable and then using that in a SUMMARIZECOLUMN:

EVALUATE

VAR enriched_dim_product = 
	ADDCOLUMNS(
		dim_product,
		"fruit_or_not", IF(dim_product[product_type] = "fruit", "fruit", "non_fruit")
	)
		
RETURN

SUMMARIZECOLUMNS(
	dim_country[country_name],
	enriched_dim_product[fruit_or_not],	
	"sales", SUM(fact_sales[sales])
)

But this throws an error saying it cannot find the table called 'enriched_product_table'. I know I could access 'enriched_product_table' and the 'fruit_or_not' column using SUMMARIZE, but then I can't figure out how to also bake in the country name and the sum of sales.

Any tips?

Thanks!

[tackytechtom · Accepted solution]

Hi @csaba09 ,

In your case (which I pressume is a simplification of the real problem), I would have just added the "fruit_or_not" attribute on to the product dimension (calculated column or in Power Query). Then Power BI woild do the grouping for you due to filter transition:

Would that one also work in your case? I mean you would need the fruit_or_not attribute anyway if you'd like to show it in a graph?

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tamerj1 · Accepted solution]

Hi @csaba09 

please try

EVALUATE
VAR T1 =
    SUMMARIZE (
        fact_sales,
        dim_country[country_name],
        dim_product[product_type],
        "@sales", SUM ( fact_sales[sales] )
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" )
    )
RETURN
    SUMMARIZE (
        T2,
        [country_name],
        [fruit_or_not],
        "sales",
            VAR Country = [country_name]
            VAR Fruit = [fruit_or_not]
            RETURN
                SUMX (
                    FILTER ( T2, [country_name] = Country && [fruit_or_not] = Fruit ),
                    [@sales]
                )
    )

[tackytechtom · Accepted solution]

Hi @csaba09 ,

I only now noticed that you were explicitly asking for a table variable. Here my shot:

tableNew = 
VAR _table1 =
SUMMARIZE (
    fact_sales,
    dim_country[country_name],
    dim_product[product_type],
    fact_sales[sales]
)
VAR _table2 =
ADDCOLUMNS (
    _table1,
    "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" )
)
RETURN
GROUPBY ( 
    _table2, 
    [country_name], 
    [fruit_or_not], 
    "sales", SUMX(CURRENTGROUP(), [sales]) 
)

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tamerj1 · Accepted solution]

Hi @csaba09 
Actually there is a much simpler solution by defining a new column

DEFINE
    COLUMN dim_product[fruit_or_not] =
        IF ( dim_product[product_type] = "fruit", "fruit", "non_fruit" )

EVALUATE
SUMMARIZECOLUMNS (
    dim_country[country_name],
    dim_productt[fruit_or_not],
    "sales", SUM ( fact_sales[sales] )
)

[csaba09]

Now this feels a lot cleaner @tamerj1 code-wise and also because we only summarize/group once instead of twice. Many thanks, this is super!

[tackytechtom · Accepted solution]

Hi @csaba09 ,

I only now noticed that you were explicitly asking for a table variable. Here my shot:

tableNew = 
VAR _table1 =
SUMMARIZE (
    fact_sales,
    dim_country[country_name],
    dim_product[product_type],
    fact_sales[sales]
)
VAR _table2 =
ADDCOLUMNS (
    _table1,
    "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" )
)
RETURN
GROUPBY ( 
    _table2, 
    [country_name], 
    [fruit_or_not], 
    "sales", SUMX(CURRENTGROUP(), [sales]) 
)

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[csaba09]

Thanks @tackytechtom , the query works well! And as you mentioned in your other reply of course it's probably best to include the extra column in the model, spot on! Only this time I wanted to see if we can do the magic already when DAX querying the SSAS cube because with the real data I'm working with this will make a BIG difference in the row number that arrives to the Query Editor from the cube 🙂

[tamerj1 · Accepted solution]

Hi @csaba09 

please try

EVALUATE
VAR T1 =
    SUMMARIZE (
        fact_sales,
        dim_country[country_name],
        dim_product[product_type],
        "@sales", SUM ( fact_sales[sales] )
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" )
    )
RETURN
    SUMMARIZE (
        T2,
        [country_name],
        [fruit_or_not],
        "sales",
            VAR Country = [country_name]
            VAR Fruit = [fruit_or_not]
            RETURN
                SUMX (
                    FILTER ( T2, [country_name] = Country && [fruit_or_not] = Fruit ),
                    [@sales]
                )
    )

[csaba09]

Thank you @tamerj1 , works like a charm!

[tackytechtom · Accepted solution]

Hi @csaba09 ,

In your case (which I pressume is a simplification of the real problem), I would have just added the "fruit_or_not" attribute on to the product dimension (calculated column or in Power Query). Then Power BI woild do the grouping for you due to filter transition:

Would that one also work in your case? I mean you would need the fruit_or_not attribute anyway if you'd like to show it in a graph?

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/