How to make the total percentage correct?

Hi Steven,

Thanks for reaching out. I'm not sure where the Total and the second Won (%) fields are coming from in your example, but you can get this to work by making both of them Calculated Fields.

Here's my example, which you'll see is correct:

I have my Total Calculated Field set to the following:

I'm assuming each field you're adding up have SUM aggregations applied to them, but if that's not the case, just make sure your aggregations align with what's in the report builder. In my example you can see both my dateint_id and dateint_int fields have a SUM aggregation applied:

so the SUM must go in the Calculated Field. If you don't have an aggregation on them, don't aggregate from within the Calculated Field.

Anyways, for the Percent of Total column, I made a second Calculated Field:

With a SUM aggregation on dateint_id and divided by an un-aggregated Total, since there's no aggregation applied on my Total column, and multiplied it by 100 to make it a percentage.

I then have the Calculated Total option available:

If you divide 201591 by 31901815, then multiply by 100, you'll see that 0.63 is indeed the correct result. Which means if you set up the report in this way your end result would be the desired 66.4% value.

Please give this a try and let me know how goes.

Regards,

Mike

Hi Mike,

Thanks for you patient explanation and sorry for my unclear description about the issue.

Actually there are 5 situations, namely Won, Lost, Pending, No opportunity, and Re-escalated, we count distinctly the case ID for each situation for analysis, and we remove Re-escalated when need to summarize all count of case ID, that's why the total is called 'Total excluded Re_escalated'.

Previously, I used a complicated calculated field to get the percentage of each column including the modified Total, hence there is no issue about the column aggregation. But there was always a small gap from 100% for Total excluded Re_escalated %, I suppose it's because there was no decimal figure for calculated total shown.

Thereafter I had to change to take Advanced function - Percentage again column on each column, and no calculated fields applied, then Total excluded Re_escalated % was 100%. Unfortunately, I can only select %average for column aggregation which seems a bit reasonable. But the user don't accept this since it's far from the correct result.

As per your above advice, I'm still not sure if can finalize my trouble. Can you please guide me further? Hope you can understand the dilemma.

Thank you.

Hi Mike,

Precisely, by my complicated calculated field the calculated total can show decimal well, it's the aggregation for each line of records conceal the decimal, %average is the one I select for each line.

Hi Steven,

Thanks for your response. I think it best if we just hop on a quick share so you can show me your setup and I can take some notes and try to get this figured out on my end. I should note though that report creation isn't generally in the realm of Support, but rather Consulting, but I'm happy to take a quick look in case there's something that stands out, and being guided through the report setup quickly should help clear that up. I suspect we're in incompatible time zones, but I'm available tomorrow 8 AM PST - 1 PM PST (US), then again on Friday 8:30 AM PST - 4 PM PST. If these times don't work, please let me know your availability and I can reach out to one of my global colleagues to assist further.

Regards,

Mike

Hi Mike,

Thank you for this kind consideration, and sorry for my confusion on how to use the community (about the difference between topic and ticket, about report building being out of scope etc.).

Yes for the jet lag, I'm located in China and support Yellowfin report change request and incident within the group company. I'm wondering how to share my setting with you currently.

There is a clue for this issue I met. The concealed decimals may be caused by my data type, but it's difficult to change now, here you can refer to below SQL on which I get the figure of the situations.

CASE WHEN case_stat = 'WON' THEN COUNT(DISTINCT ( case_id )) ELSE 0 END AS "WON",

CASE WHEN case_stat = 'LOST' THEN COUNT(DISTINCT ( case_id )) ELSE 0 END AS "LOST",

CASE WHEN case_stat = 'NO OPPORTUNITY' THEN COUNT(DISTINCT ( case_id )) ELSE 0 END AS "NO OPPORTUNITY",

case_id (nvarchar(50),null)

Hi Mike,

After I amend the SQL as below, I found the decimal appeared in the report. The sum of the total can never to 100% by nature of itself I have to accept. Thank you very much!

CASE WHEN case_stat = 'WON' THEN CAST(COUNT(DISTINCT(case_id)) ASdecimal(37,8)) ELSE 0 END AS "WON",

Hi Steven,

Thanks for your response. I believe your Account Manager, Courtney, reached out to you already, but a Consultant should be reaching out to discuss what you're looking to accomplish. I'll leave this ticket open though and check back in next week in case I don't hear back from you just to make sure this was addressed and that you're in a better spot.

Regards,

Mike

Hi Steven,

I just wanted to check in and see how things went with this. Do you require anything else regarding this?

Regards,

Mike

Hi Steven,

I'm going to go ahead and mark this one as Completed since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

Hi Mike,

Yes, please close this ticket and thank you for your kind support.

Regards,

Steven

Hi Steven,

You're most welcome! Please don't hesitate to reach back out with any other questions or concerns.

Regards,

Mike