Total not getting calculated as expected

[amrutamore2594]

Hi all,

I am trying to work out a total of particular columns but not able to do it as expected.

As you can see in the table above there are payment % and sum of 10,12 & 15. The formula for Tier 1 and Tier 2 is as below,

Tier 1_ = IF('Report'[Payment%] < 0.7, ((5*'Report'[Sum of 10])+(5*'Report'[Sum of 12])+(10*'Report'[Sum of 15])))

Tier 2_ = IF('Report'[Payment%] >= 0.7, ((37*'Report'[Sum of 10])+(37*'Report'[Sum of 12])+(48*'Report'[Sum of 15])))

When the total is getting calculated, it is not adding all the numbers in Tier 1 and Tier 2 respectively but just calculating based on total Payment% i.e. 55.91%

Does anyone know how to work this out?

[v-junyant-msft · Accepted solution]

Hi @amrutamore2594 ,

I was not able to reproduce your situation:

May I ask how you created these four columns?

Generally the reason for errors in total is basically a problem with the context. Can you provide a bit of your sample data as well as the DAX, or maybe a pbix file? It's impossible to know what the problem is with just the screenshots and DAX you provided.

Or you can try, creating two MEASURES instead of a calculated column:

Tier 1_measure = IF(MAX('Report'[Payment%]) < 0.7, ((5*SUM('Report'[Sum of 10]))+(5*SUM('Report'[Sum of 12]))+(10*SUM('Report'[Sum of 15]))))

Tier 1_values = SUMX(VALUES(Report[Payment%]), [Tier 1_measure])

Then put the Tier 1_values into the table visual:

The total is right, and the same as Tier 2.

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

[amrutamore25]

I am still not able to replicate the calculations as it suppose to be. This post isn't allowing me to add a .pbix file. But I'll try and add more details about calculations. Sample data looks something like this,

ClientReference	Company Name	Pin	Number	Sales Person's name	SignWE	FinalStatus	CCCancelled	NetSale	PSX	ActualMCAmount	ClientCanxDate	ClientCanxCode	CLCancelled	DD1Sent	DD1Success	DD1Fail	DD1CanxCode	DD2Sent	DD2Success	DD2Fail	DD2CanxCode	DDSuccesCount	DDStartDate	donation_amount	AgeofDonor
288000	RealStar	1111	FR2809	Aida Bugg	17/12/2023	01 - Completed	0	1	C	70	NULL		1	0	0	0		0	0	0		1	15/01/2024	10	58
288910	RealStar	1111	FR4546	Teri Dactyl	17/12/2023	01 - Completed	0	1	C	70	NULL		1	0	0	0		0	0	0		2	15/01/2024	10	35
289015	RealStar	1111	FR2809	Aida Bugg	17/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	U25	0	NULL		1	0	0	0		0	0	0		0	15/01/2024	10	24
289256	RealStar	1111	FR4558	Peg Legge	17/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	15/01/2024	10	62
289518	RealStar	1111	FR4546	Teri Dactyl	17/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		1	15/01/2024	10	33
289531	RealStar	1111	FR4558	Peg Legge	17/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	15/01/2024	10	62
289874	RealStar	1111	FR4546	Teri Dactyl	17/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	15/01/2024	10	59
290655	RealStar	1111	FR2809	Aida Bugg	17/12/2023	01 - Completed	0	1	C	70	NULL		1	0	0	0		0	0	0		0	15/01/2024	10	39
290904	RealStar	1111	FR109	Allie Grater	24/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	25
290971	RealStar	1111	FR109	Allie Grater	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		1	22/01/2024	10	54
291450	RealStar	1111	FR4545	Olive Yew	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		0	22/01/2024	10	35
291479	RealStar	1111	FR109	Allie Grater	24/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	61
291487	RealStar	1111	FR4546	Teri Dactyl	24/12/2023	03 - No - Refused Presentation	0	1	C	70	NULL		1	0	0	0		0	0	0		0	22/01/2024	10	31
291525	RealStar	1111	FR109	Allie Grater	24/12/2023	03 - No - Refused Presentation	0	1	C	70	NULL		1	0	0	0		0	0	0		4	22/01/2024	10	49
291674	RealStar	1111	FR4545	Olive Yew	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	NSD	0	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	26
291685	RealStar	1111	FR4545	Olive Yew	24/12/2023	10 - Not Contacted - No Telematch	0	1	NSD	0	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	26
291982	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	01 - Completed	0	1	C	70	NULL		1	0	0	0		0	0	0		2	22/01/2024	10	37
292254	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	33
292373	RealStar	1111	FR109	Allie Grater	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	FNP	0	NULL		1	0	0	0		0	0	0		1	22/01/2024	10	38
292378	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	49
292633	RealStar	1111	FR4545	Olive Yew	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	26
292669	RealStar	1111	FR4545	Olive Yew	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	74
292683	RealStar	1111	FR109	Allie Grater	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		3	22/01/2024	10	71
292988	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	36
293146	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		1	22/01/2024	10	28
293261	RealStar	1111	FR109	Allie Grater	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		70	NULL		0	0	0	0		0	0	0		4	22/01/2024	10	47
293268	RealStar	1111	FR109	Allie Grater	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		3	22/01/2024	10	67
293269	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1		75	NULL		0	0	0	0		0	0	0		4	22/01/2024	12	51
293374	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		1	22/01/2024	10	55
293474	RealStar	1111	FR4549	Maureen Biologist	24/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		0	22/01/2024	10	28
294718	RealStar	1111	FR109	Allie Grater	31/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	01/02/2024	10	64
294745	RealStar	1111	FR109	Allie Grater	31/12/2023	09 - Not Contacted - Wrong Number	0	1	75U	0	NULL		0	0	0	0		0	0	0		4	01/02/2024	10	75
294971	RealStar	1111	FR4545	Olive Yew	31/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		2	01/02/2024	10	28
294999	RealStar	1111	FR4545	Olive Yew	31/12/2023	07 - Not Contacted - Not Available In Time Period	0	1	C	70	NULL		1	0	0	0		0	0	0		2	01/02/2024	10	34
295194	RealStar	1111	FR109	Allie Grater	31/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	01/02/2024	10	67
295209	RealStar	1111	FR109	Allie Grater	31/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	01/02/2024	10	54
295213	RealStar	1111	FR4545	Olive Yew	31/12/2023	01 - Completed	0	1		70	NULL		0	0	0	0		0	0	0		4	01/02/2024	10	35
295684	RealStar	1111	FR109	Allie Grater	31/12/2023	09 - Not Contacted - Wrong Number	0	1		70	NULL		0	0	0	0		0	0	0		2	01/02/2024	10	50

The results I am getting are as below,

The Payment% is a measure itself calculated as,

Payment% = DIVIDE(SUM(Sheet1[Payment]), COUNT(Sheet1[ClientReference]))

The Payment column above is calculated as,

Payment = IF('Sheet1'[DDSuccesCount] >= 3, 1, 0)

We have,

Sum of 10 = Sum(Sheet1[10])

Sum of 12 = Sum(Sheet1[12])

Sum of 15 = Sum(Sheet1[15])

The calculated columns are as below,

10 = IF('Sheet1'[donation_amount] = 10 && 'Sheet1'[Payment] = 1, 1, 0)

15 = IF('Sheet1'[donation_amount] = 15 && 'Sheet1'[Payment] = 1, 1, 0)

12 = IF('Sheet1'[donation_amount] = 12 && 'Sheet1'[Payment] = 1, 1, 0)

 

Tier 1_ = IF('Report'[Payment%] < 0.7, ((5*'Report'[Sum of 10])+(5*'Report'[Sum of 12])+(10*'Report'[Sum of 15])))

Tier 2_ = IF('Report'[Payment%] >= 0.7, ((37*'Report'[Sum of 10])+(37*'Report'[Sum of 12])+(48*'Report'[Sum of 15])))