cancel
Showing results 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

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.

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
Community Support

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.

2 REPLIES 2
Community Support

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.

Regular Visitor

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

Announcements

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 Monthly Update - June 2024

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

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
Top Kudoed Authors