The classic problem where matrix total is not correct

[AFra]

Hi all, 

I created a measure to count an amount of calls, such as : 

CALCULATE(DISTINCTCOUNT(CallHistory[phonenum])+ COUNTBLANK(CallHistory[phonenum]),

CallHistory[CallType]="C",

FiltreAppels[Durée]>=30,

FiltreAppels[Durée]<=120,

year(CallHistory[StartOfCall])=2020

)

When I display data by month I get a total of 781 instead of 908 : 

How can I adapt my formula so I get the correct total? I saw a lot of posts on that topic but any of the solutions fits to this case. 

Thanks in advance for your precious help! 

[v-luwang-msft]

Hi @AFra ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[PaulDBrown]

Assuming you have a Date table and the fields in the visual are from the Date table try:

With total = SUMX(Date table, [Your current measure])

[AFra]

I'm already using distinctcount : 

sumx(data table, CALCULATE(DISTINCTCOUNT(CallHistory[phonenum])+ COUNTBLANK(CallHistory[phonenum]),

CallHistory[CallType]="C",

FiltreAppels[Durée]>=30,

FiltreAppels[Durée]<=120,

year(CallHistory[StartOfCall])=2020

)

any other idea why this is not working correctly? 

thanks a lot for your help!

[PaulDBrown]

Try splitting it into 2 measures:

measure 1 = 

CALCULATE(DISTINCTCOUNT(CallHistory[phonenum])+ COUNTBLANK(CallHistory[phonenum]),

CallHistory[CallType]="C",

FiltreAppels[Durée]>=30,

FiltreAppels[Durée]<=120,

year(CallHistory[StartOfCall])=2020

)

Final measure = SUMX(Data table, [measure 1])

[AFra]

tried that too, but it's not working 😕 

here's a sample of the data, if anyone feels like trying to solve this.. 

https://www.transfernow.net/dl/20211014AHDaK8RW/wHYxz7mY

[PaulDBrown]

See if this works (ive added a Date Table to the model)

With Totals =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( CallHistory, 'Date Table'[Month], CallHistory[Clip] ),
        "_result",
            CALCULATE (
                DISTINCTCOUNT ( CallHistory[Clip] ),
                CallHistory[CallType] = "C",
                CallHistory[Time] >= 30,
                CallHistory[Time] <= 120,
                YEAR ( CallHistory[StartOfCall] ) = 2020
            )
    ),
    [_result]
)

 I've attached the sample PBIX file

[AFra]

thanks for your suggestion, but here both month totals and grand total are incorrect 😕 

if I check manually in the raw data how many distinct clip for january, with calltype = C and Time between 30 and 120, I get 170 clips and not 181.. 

Also, if I sum manually all the values for each month, I get 1521 and not 1821. How is this possible? What's wrong here? 

[PaulDBrown]

I'm not sure how you are calculating the values. Here is the table with a simple DISTINCTCOUNT for CallHistory [Clip], but applying the filters to the actual Table.

In PBI:

In Excel:

 Attached is the new PBIX file

[AFra]

Hi @PaulDBrown , thanks for your suggestion, it indeed changed the grand total that now represents the right total, but it also changed all the results for each month : 

If I compare to the raw data, I see that for example for january there are 108 distinct contacts, and 141 total contacts (some contacts have the same ID). So this solution doesn't seem to be adapted. I need the measure to calculate distinct counts for every month (e.g. someone who called twice in a month will be counted once, but if he calls again next month it will be counted once in each month). Do you have any idea how can I get that? 

Thanks in advance! 

Ana 
 

[Anonymous]

Hi guys, I have a problem with my power bi. I needed the ID's count of a factual table to then make the segmentation and analyze the same total by the dimensions it is on. I used DistinctCount on Fact Table l'd to get the count But when I put the measure in tables and I placed the appropriate segmentations (obtained in the dimension tables) that I had a problem with the totals. I have the right total but the sum of the lines does not give the correct value.I don't know how this distribution of values, but the correct value obtained by counting of the IDs is this. 

Scheme

Table_F_: id, Id_seg, id_status, id_dir, Count_Id
Table_ D_Mon: id_seg, mon_name
Table_D_Status: id_status, status_name
Table_D_Dir: id_dir, dir_name

[PaulDBrown]

@Anonymous 
I suggest you start a new topic for your question (and please provide sample data and a depiction of the expected outcome)

[PaulDBrown]

Try something along the lines of:
With total = SUMX(Date table, DISTINCTCOUNT(Table[Contact ID]))
but beware that the total will de the sum of each month. If you want the total to be the distinctcount of IDs overall, the measure should be

Distinct ID = DISTINCTCOUNT(Table[ID])

[moizsherwani]

This classic problem unforutnately doesn't have a one size fits all answer and there could be a number of things which is causing this, the most important thing to remember is that the total is not the total of all the values on top (which users from the Excel world are used to). Think of the total as another calculation where the formula is evaulated with all the filters from the rows above removed (i.e. no months are taken into context but # call perm <2 is and see why there are more rows). That said and without having a look at anything I would suggest splitting your measure into two, i.e. one for distinct count and one for countblank and drop them in the table, this might get you closer to where the discrepency is. 

Regards,

Moiz

If this post helps, please "Accept" it as Solution to help other members find it.

[AFra]

Hi Moiz, 

thanks for your response. Indeed, I tried to split the measure in two, but the problem, and you mentionned it, is that I need a distinctcount by month, and the total calculates the distinct count for the total year. Thus, a phonenum that called once in january and twice in february will be counted once in january and once in february, but only one time in the total. 

Is there a way to integrate the idea of "distinctcount by month" in the measure?