DAX - Measure (Whole Total for Product and Group) - Context sensitive

[zapppsr]

Hi guys, thanks for taking your time to help me.

I need something I understand to be very simple, but on the other hand is tricking me.
I used ALL and ALL SELECTED to try and solve my problem, but I'm still learning filters, groups and all...

I need a grand total for Product and Group in a measure that by context know which products and groups to sum.
In fact they will be 2 measures, one for product and one for group.

Below, a table with the desired results:

 

[Matt Allington]

Product total :=calculate(sum(table[value]),all(table[product]))

same for group

 

[zapppsr]

Hi Matt, thanks for replying. I'm sorry but it didn't work:

Here is the file: Download PS: Portuguese Syntax

Remember, I need the total per product and per group. Not

 

[zapppsr]

Hi again!

I found this solution:


Product total:=calculate(sumx(Tabela1;Tabela1[Value]);FILTERS(Tabela1[Product]);ALL(Tabela1))

Group total:=calculate(sumx(Tabela1;Tabela1[Value]);FILTERS(Tabela1[Group]);ALL(Tabela1))

 

[scottsen]

I literally had to go look at what the FILTERS() function was

Typically you will see this as

Product total:=calculate(sumx(Tabela1;Tabela1[Value]);ALLEXCEPT(Tabela1, Tabela1[Product]))
or
Product total:=calculate(sumx(Tabela1;Tabela1[Value]);ALL(Tabela1); VALUES(Tabela1[Product]))

But now, I'm gonna be curious about this crazy FILTERS() function

 

[zapppsr]

HI scottsen!

Thanks for replying. I found your solution more interesting, because it can calculate the group value without having to show the group in the pivot table. I can calculate only showing the product name and it knows in what group the product is in. That is more what I'm looking for. Thanks for showing the way.

 

[zapppsr]

Since we are taking about this matter, I came across another problem:

In the first scenario, All groups have all products.

Now, lets suppose that in the South Region, the Vehicles group doesn't have Bus.

Is it possible for that specific group, the group total consider only the products present in South Vehicles? That is: 330. It still checks for the total of all products in that group in the whole company, but if the product is not present in one group, it doesn't sum...

Is it clear? Can you help me?