Totals not showing correctly in a table

[jonclay]

Hi everyone,

I'm certain that a simple solution to my query exists, but I can't seem to figure it out at the moment!

I have a table that holds finance data called all_relevant_transactions. On this table I have several Calculated Fields that work out various totals. An example is shown below:

Total_Rec_201112_Gross =

CALCULATE(

    SUM (all_relevant_transactions[destcodetotal]),

    (all_relevant_transactions[statuscodename]="Paid"),

    DATESBETWEEN(

        all_relevant_transactions[si_transactiondateofpayment],DATE(2011,9,01),DATE(2012,8,31)))

The Calculated Fields themselves are working fine in principle, but I have a problem when showing the Totals in a table. The all_relevant_transactions table contains ALL financial transactions for ALL contacts, and it has a relationship to a Contact table via an N:N relationship that only contains a few of the contacts that exist in the all_relevant_transactions table. So, the all_relevant_transactions table may contain financial transactions for 1000 contacts, but the Contact table may only have 12 contacts in it (this is because it has been filtered down).

So, when I show this data in a table and select the Totals option, it is giving me a total for ALL financial transactions relating to the relevant Calculated Field from the all_relevant_transactions table. The example below shows you what I mean:

What I really need is the total for each column to show the total of all the rows and not the overall total relating to the Calculated Field. I suspect that this is something to do with the way that I've created the Calculated Fields themselves, but I'm unable to work out what I've done wrong!

Any help would be much appreciated.

Many thanks
Jon

[v-yadongf-msft · Accepted solution]

Hi @jonclay ,

Totals not showing correctly is a very common thing in Power BI. Maybe you should know ISINSCOPE function and ALL or ALLEXPECT function.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

ALL function (DAX) - DAX | Microsoft Learn

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

Since I can't see your data, here's a sample for your reference.

You can observe that the totals of the two calculated columns are not the same.

The DAX formula of the two calculated columns:

% = 
IF (
    ISINSCOPE ( 'Table'[School] ),
    DIVIDE ( [Count], [Total count 2] ),
    DIVIDE ( [Total count 2], CALCULATE ( [Count], ALL ( 'Table' ) ) )
)

% 2 = DIVIDE ( [Count], [Total count 2] )

Best regards,

Yadong Fang

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