topic Re: Grouping Question in DAX Commands and Tips

Grouping Question

MandoMongo — Fri, 03 May 2024 09:54:28 GMT

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...?

Re: Grouping Question

sjoerdvn — Fri, 03 May 2024 09:53:17 GMT

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.

Re: Grouping Question

Anonymous — Mon, 06 May 2024 09:04:33 GMT

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.

Re: Grouping Question

MandoMongo — Tue, 07 May 2024 10:10:16 GMT

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.

Re: Grouping Question

MandoMongo — Tue, 07 May 2024 10:22:01 GMT

Thank you Clara,

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