Grouping Question

[MandoMongo]

I'm trying to write a dax measure that will group Sales by Product. 

I can see via a table with Sales and Product columns what the result should be...

Yet the following measure is off somehow...

Gross Sales by Product =

CALCULATE(

    SUM(financials[Gross Sales]),

    ALLEXCEPT(financials,financials[Product])

)

What am I doing wrong?

EDIT: There was somehow a country filter applied to the first table, it's calculating the same as my dax measure now. So just to confirm that application of DAX code works to group by...?

[v-kaiyue-msft · Accepted solution]

Hi @MandoMongo ,

I want to confirm the valuable input provided by @sjoerdvn . However, I noticed that more detail is needed to fully understand this.

As @sjoerdvn  said, you just need to put the fields product and gross sales into the table visual object and it shows the grouped results.

If you want to verify that the filtered calculations are correct, you can create measure to do so.

Measure =
CALCULATE(SUM(financials[Gross Sales])),FILTER(ALL(financials),'financials'[Product] = "VTT" && 'financials'[Country] = "France"))

MEASURE shows the same results as in the table, so it's grouping and summing correctly.

Also, a deeper understanding of the concept of context may help you in solving the problem. Context is one of the most important DAX concepts to understand.There are two types of context in DAX: row context and filter context. We will first look at row contexts. More details can be found in the documentation: Learn DAX basics in Power BI Desktop - Power BI | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MandoMongo]

Thank you Clara, 

This is correct, thank you for demonstrating the way to write this as DAX. It's very helpful.

[sjoerdvn]

I don't understand what you're trying to do. If you have a product column, or better yet a related product dimension, you do not need any specific measures to display the sum per product. 

[MandoMongo]

Hi, 

I know the measure is technically unnecessary it's just that I'm trying to understand how to approach it in DAX . I'm still trying to really understand the DAX approach and I am probably still thinking in SQL methods - but trying to see how you would do the equivalent if I had to in DAX. Appreciate you getting back on this.