Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amrutamore2594
New Member

Total not getting calculated as expected

Hi all,

 

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

 

amrutamore2594_1-1716373050096.png

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?

 

 

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @amrutamore2594 ,

I was not able to reproduce your situation:

vjunyantmsft_0-1716427470973.png

May I ask how you created these four columns?

vjunyantmsft_1-1716427493057.png

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:

vjunyantmsft_2-1716428136543.png

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.

View solution in original post

2 REPLIES 2
v-junyant-msft
Community Support
Community Support

Hi @amrutamore2594 ,

I was not able to reproduce your situation:

vjunyantmsft_0-1716427470973.png

May I ask how you created these four columns?

vjunyantmsft_1-1716427493057.png

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:

vjunyantmsft_2-1716428136543.png

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.

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,


ClientReferenceCompany NamePinNumberSales Person's nameSignWEFinalStatusCCCancelledNetSalePSXActualMCAmountClientCanxDateClientCanxCodeCLCancelledDD1SentDD1SuccessDD1FailDD1CanxCodeDD2SentDD2SuccessDD2FailDD2CanxCodeDDSuccesCountDDStartDatedonation_amountAgeofDonor
288000RealStar1111FR2809Aida Bugg17/12/202301 - Completed01C70NULL 1000 000 115/01/20241058
288910RealStar1111FR4546Teri Dactyl17/12/202301 - Completed01C70NULL 1000 000 215/01/20241035
289015RealStar1111FR2809Aida Bugg17/12/202307 - Not Contacted - Not Available In Time Period01U250NULL 1000 000 015/01/20241024
289256RealStar1111FR4558Peg Legge17/12/202301 - Completed01 70NULL 0000 000 415/01/20241062
289518RealStar1111FR4546Teri Dactyl17/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 115/01/20241033
289531RealStar1111FR4558Peg Legge17/12/202301 - Completed01 70NULL 0000 000 415/01/20241062
289874RealStar1111FR4546Teri Dactyl17/12/202301 - Completed01 70NULL 0000 000 415/01/20241059
290655RealStar1111FR2809Aida Bugg17/12/202301 - Completed01C70NULL 1000 000 015/01/20241039
290904RealStar1111FR109Allie Grater24/12/202301 - Completed01 70NULL 0000 000 422/01/20241025
290971RealStar1111FR109Allie Grater24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 122/01/20241054
291450RealStar1111FR4545Olive Yew24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 022/01/20241035
291479RealStar1111FR109Allie Grater24/12/202301 - Completed01 70NULL 0000 000 422/01/20241061
291487RealStar1111FR4546Teri Dactyl24/12/202303 - No - Refused Presentation01C70NULL 1000 000 022/01/20241031
291525RealStar1111FR109Allie Grater24/12/202303 - No - Refused Presentation01C70NULL 1000 000 422/01/20241049
291674RealStar1111FR4545Olive Yew24/12/202307 - Not Contacted - Not Available In Time Period01NSD0NULL 0000 000 422/01/20241026
291685RealStar1111FR4545Olive Yew24/12/202310 - Not Contacted - No Telematch01NSD0NULL 0000 000 422/01/20241026
291982RealStar1111FR4549Maureen Biologist24/12/202301 - Completed01C70NULL 1000 000 222/01/20241037
292254RealStar1111FR4549Maureen Biologist24/12/202301 - Completed01 70NULL 0000 000 422/01/20241033
292373RealStar1111FR109Allie Grater24/12/202307 - Not Contacted - Not Available In Time Period01FNP0NULL 1000 000 122/01/20241038
292378RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01 70NULL 0000 000 422/01/20241049
292633RealStar1111FR4545Olive Yew24/12/202307 - Not Contacted - Not Available In Time Period01 70NULL 0000 000 422/01/20241026
292669RealStar1111FR4545Olive Yew24/12/202307 - Not Contacted - Not Available In Time Period01 70NULL 0000 000 422/01/20241074
292683RealStar1111FR109Allie Grater24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 322/01/20241071
292988RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01 70NULL 0000 000 422/01/20241036
293146RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 122/01/20241028
293261RealStar1111FR109Allie Grater24/12/202307 - Not Contacted - Not Available In Time Period01 70NULL 0000 000 422/01/20241047
293268RealStar1111FR109Allie Grater24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 322/01/20241067
293269RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01 75NULL 0000 000 422/01/20241251
293374RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 122/01/20241055
293474RealStar1111FR4549Maureen Biologist24/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 022/01/20241028
294718RealStar1111FR109Allie Grater31/12/202301 - Completed01 70NULL 0000 000 401/02/20241064
294745RealStar1111FR109Allie Grater31/12/202309 - Not Contacted - Wrong Number0175U0NULL 0000 000 401/02/20241075
294971RealStar1111FR4545Olive Yew31/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 201/02/20241028
294999RealStar1111FR4545Olive Yew31/12/202307 - Not Contacted - Not Available In Time Period01C70NULL 1000 000 201/02/20241034
295194RealStar1111FR109Allie Grater31/12/202301 - Completed01 70NULL 0000 000 401/02/20241067
295209RealStar1111FR109Allie Grater31/12/202301 - Completed01 70NULL 0000 000 401/02/20241054
295213RealStar1111FR4545Olive Yew31/12/202301 - Completed01 70NULL 0000 000 401/02/20241035
295684RealStar1111FR109Allie Grater31/12/202309 - Not Contacted - Wrong Number01 70NULL 0000 000 201/02/20241050

 

The results I am getting are as below,

amrutamore25_0-1717512595331.png

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])))
 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors