Count is Wrong When Field is Removed from Matrix/Table

[Anonymous]

I have a measure counting the number of months based on business logic, called Total Billable Units.  This measure has been validated and works correctly in a matrix when the fields Name and Loan Number are in the visual.  However, when I remove the Loan Number field, the numbers get inflated and is no longer correct.  I expect the measure to summarize the number of Total Billable Units by Name.  Here is what the measure looks like:

Total Billable Units = 
VAR DOT =
    CALCULATE (
        SUMX (
            VALUES ( LoanContracts[LoanNumber] ),
            IF (
                (
                    IF (
                        ISBLANK ( [DOT Received Date] ),
                        MONTH ( TODAY () ),
                        MONTH ( [DOT Received Date] )
                    )
                        - MONTH ( [Funds Released Date] ) - 1
                ) < 0,
                0,
                IF (
                    ISBLANK ( [DOT Received Date] ),
                    MONTH ( TODAY () ),
                    MONTH ( [DOT Received Date] )
                )
                    - MONTH ( [Funds Released Date] ) - 1
            )
        )
    )
VAR Title =
    CALCULATE (
        SUMX (
            VALUES ( LoanContracts[LoanNumber] ),
            IF (
                (
                    IF (
                        ISBLANK ( [Title Received Date] ),
                        MONTH ( TODAY () ),
                        MONTH ( [Title Received Date] )
                    )
                        - MONTH ( [Funds Released Date] ) - 1
                ) < 0,
                0,
                IF (
                    ISBLANK ( [Title Received Date] ),
                    MONTH ( TODAY () ),
                    MONTH ( [Title Received Date] )
                )
                    - MONTH ( [Funds Released Date] ) - 1
            )
        )
    )
RETURN
    DOT + Title

Loan Number is from LoanContracts(1:) table and Name is from another table(:M) that shares a 1:M relationship.

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Do the result data obtained for your measure agree with the data you provided? If so, you can refer to the following：

step1,Import the data you provide:

Since your [Total Billable Units] are actually measures，in my test,totalb2 is measure ,just as  your actual [Total Billable Units]：

totalb2 = MAX(LoanContracts[Total Billable Units])

Step 2,

use the below measure:

Measure = SUMX(LoanContracts,[totalb2])

Get as what you want get:

Wish it is helpful for you!

Best Regards

Lucien

[Greg_Deckler]

@Anonymous I suspect strongly that you have a measure totals problem. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

This may also be of use:

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[Anonymous]

This seemed very promising but when I tried it out, its still not giving me the right numbers 😞 the numbers are only correct when I have Lender Name and Loan Number in the table.  When I remove Loan Number from the table, the measure is not summing correctly by the Lender Name.

[Greg_Deckler]

@Anonymous Really difficult to be specific without sample data.

[Anonymous]

Here's some sample data:

Name	Loan Number	Funds Released Date	DOT Received Date	Title Received Date	Total Billable Units
ABC	0	1/8/2021 0:00	2/16/2021 17:51		5
ABC	1	1/11/2021 0:00	1/20/2021 18:43	4/6/2021 21:32	2
ABC	2	1/7/2021 0:00	1/20/2021 18:44	4/1/2021 23:17	2
ABC	3	2/11/2021 0:00	2/22/2021 17:35	2/22/2021 17:35	0
ABC	4	1/20/2021 0:00	2/8/2021 20:11		5
ABC	5	1/26/2021 0:00	2/8/2021 20:09	7/9/2021 16:27	5
ABC	6	2/11/2021 0:00	2/22/2021 17:29	4/13/2021 21:10	1
ABC	7	2/5/2021 0:00	2/22/2021 17:15	5/18/2021 17:22	2
DE	8	1/19/2021 0:00	3/5/2021 21:38	3/5/2021 21:38	2
DE	9	1/5/2021 0:00	3/25/2021 19:51	3/25/2021 19:48	2
F	10	1/20/2021 0:00	5/3/2021 17:39	5/3/2021 17:37	6
F	11	1/22/2021 0:00		3/2/2021 20:00	6
F	12	2/23/2021 0:00	3/2/2021 20:02	3/2/2021 20:02	0
F	13	7/21/2021 0:00	7/23/2021 22:15		0
G	14	1/5/2021 0:00	2/1/2021 21:36	2/17/2021 23:30	0
G	15	1/25/2021 0:00	3/3/2021 23:59	5/12/2021 17:56	4
G	16	3/4/2021 0:00	3/31/2021 22:18	4/23/2021 18:18	0
G	17	2/9/2021 0:00	3/4/2021 0:15	3/4/2021 0:15	0
G	18	3/16/2021 0:00	5/12/2021 19:10	5/12/2021 17:58	2
G	19	5/5/2021 0:00	6/10/2021 18:32	7/9/2021 19:03	1
G	20	6/10/2021 0:00	7/28/2021 19:11	7/9/2021 19:01	0
G	21	6/8/2021 0:00	6/29/2021 23:26	7/28/2021 19:31	0

Essentially, I want a summarized table like this:

Name	Total Billable Units
ABC	22
DE	4
F	12
G	7