Measure to sum a calculated column gives incorrect value

[Anonymous]

Hi Community,

I have a calculated column 'Wait time for Contact'. If I pull this column on a matrix (Page1 in pbi file), it gives correct values only if 'Average' of the column is selected that is:

I created a measure 'Wait time for Contact_Oct' to calculate the sum of the above mentioned column ('Wait time for Contact') for Oct 2020.

This measure doesn't give the correct value.

Just to test, if I pull the sum of 'Wait time for Contact' column on a table visual, then also the values for Oct 2020 is wrong.

Both visuals & Expected value is on Page3 in my following PBI file:

https://1drv.ms/u/s!Ag919_pO_UKrgQwdzaPqiwm7NcmX?e=RePjMg

Any help is much appreciated, thanks.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Sorry for late reply. I did a test. Is that result you want?

Here is my test process.

I noticed that the "PATIENTCODE" field may be the breaking point of the problem. So I created a new table as shown below.

Table = ADDCOLUMNS(SUMMARIZE('Table1',Table1[PATIENTCODE]),"s",CALCULATE(AVERAGE('Table1'[Wait time for Contact]),ALLEXCEPT(Table1,Table1[PATIENTCODE])))

Then I create a measure.

Measure 3 = SUM('Table'[s])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

Hi @Anonymous ,

Thanks for your reply. Is this the result you want? I tried the same approach, but it seems to get a different result from yours.

Best Regards,

Yuna

[Anonymous]

@Anonymous  Nope, please filter by column 'First contact '= Oct ,Not Date-month = Oct. 

I want to calculate sum of wait times where 'First Contact' = Oct. 'First contact' can be in Oct for date-month = Sep or Oct.

Also for this reason in the measure I have: USERELATIONSHIP(Table1[First contact],Datedim[Date]) which is an inactive relationship. 

[Anonymous]

@Anonymous  any update?

[Anonymous]

Hi @Anonymous ,

Sorry for that it's still not very clear. What I am confused is how to get 1736 for "wait time for contact_oct".

Could you please give me your calculation ideas?

Best Regards,

Yuna

[Anonymous]

@Anonymous  thanks for replying. 

Wait time for Contact_Oct =

CALCULATE(SUMX ( VALUES ( Table1[Wait time for Contact]), CALCULATE ( MAX (Table1[Wait time for Contact]) )),USERELATIONSHIP(Table1[First contact],Datedim[Date]),Table1[First contact]<>BLANK(),month(Table1[First contact])=month(today())-2,year(Table1[First contact])=year(today()))

I basically exported the matrixon Page1 to excel, filtered the data for 'first contact' month = Oct 2020. Then summed up the column 'Average of wait time for contact', that gives 1736. As I mentioned in my post that on the matrix I get the correct wait time for contact only by pulling it as Average.