Simple Summarized Total Calculation not getting expected result

[Anonymous]

Hi Everyone,

I'm looking for some ideas on how to solve a pretty simple problem.

In the example below, I want the column of total F to sum up the two rows above (i.e. 6,524+617 = 7,141).  Instead it follows the same calculation as the individual rows (24.04 * 303 = 7,284).

How do I get column F to sum the way I want?

I mocked up a simpler example of the relationships as I'm pulling in lots more fields and I'd like to keep this simple.  Table 3 is a summarized table of Table 2 so I can get the calculation totals I want for column E.  However, I need F to update based on the date filters and it does not when I put F in Table 3 (i.e. in Table 3 if I put F = Calculate (E*D, ALLSELECTED('Date Table'[Date])) it does not adjust for the date range filter).

Thanks for your feedback

[v-juanli-msft · Accepted solution]

Hi @Anonymous

For your first case, you could modify the measure F with the following formula.

Measure =
IF (
    COUNTROWS ( VALUES ( Sheet1[name] ) ) = 1,
    [F],
    SUMX ( VALUES ( Sheet1[name] ), [F] )
)

For second case, how about editing the relationship between table1 and table2 by turning "the cross filter direction" to both?

Best Regards

Maggie

[Anonymous]

Works perfect.  Thank you very much