Controlling subtotals formula in a Matrix

[costadom]

Hi all,

I'm looking to solve the following issue:

- I have defined three measures that are connected, meaning that measure 1 is used to assess measure 2 and measure 2 is used to assess measure 3

- After successfully achieved measure 3, I'm facing the issue that in Matric visual, the subtotal amount does not add up to the result of each individual row. I have put an example below.

Any idea on how to turn subtotals to actually add individual line instead of using the set formula in measures?

Thank  you

J

[MattAllington]

Try

=sumx(values(table[size]),[measure 3])

The issue is the way filter context works.

Read my article here http://exceleratorbi.com.au/sum-vs-sumx-in-dax/

[costadom]

Hi Matt,

Sorry for late reply and thank you very much for your help!

Your formula allmost worked !

See below another more detailled example of my issue. 

Let me know if you have any idea on how to fix it since your formula is working for 2 rows out of 5

Thank you in advance for your help!

Jerome

[MattAllington]

Can you also post an image of the visual with the formula I provided in place so I can see what that is returning

[costadom]

with the formula!

[MattAllington]

Strange.  Which tables do your Market and Size columns come from?  I can clearly see that the Size column has a value where there are blanks in the new formula, and this makes me suspicious

[costadom]

I have 4 tables with links:

Table 1: Volume by markets and size

Rows are showing different markets and within each market different product and within each product different size.

Columns are showing Markets/Size/Mths

Example

Row 1 : 123-Virginia/ABC-1000 10CL/ 10 qty

Row 2 : 123-Virginia/ABC-2000 20CL/ 50 qty

....

....

Table 2: Product Sales by markets and size

Rows are showing different markets and within each market different product and within each product different size.

Columns are showing Markets/Size/Mths

Example

Row 1 : 123-Virginia/ABC-1000 10CL/ $100

Row 2 :123-Virginia/ABC-2000 20CL/ $5000

....

....

Table 3: Size Master table

This table removes the size codes

Columns are showing Size Code/Size

Example

Row 1 : ABC-1000 10CL/ 10 CL

Row 2 : ABC-2000 20CL/ 20 CL

....

....

Table 4: Markets Master table

This table removes the Markets codes

Columns are showing Market Code/Market 

Example

Row 1 : 123-Virginia/Virginia

....

....

hope this makes sense

[MattAllington]

Sure, but which table does the SIZE column in your visual come from?  Is it from the Size master table?  It should be, but my best guess (without seeing it) is that this column is coming from a different table (just a guess)

[costadom]

See enclosed.

Thanks Matt!

Jerome

[costadom]

See enclosed.

Thanks Matt!

Jerome

[v-caliao-msft]

Hi @costadom,

I have tested it on my local environment, everything works fine.
Measure 2 = Sheet9[Measure]+11
Measure 3 = Sheet9[Measure 2]+13

In your scenario, please provide us some sample data and your measrure forumla, so that we can try to reproduce this issue and make further analysis.

Regards,

Charlie Liao