I have a calculation field:
ifelse(
region = ‘MEU’ AND year = 2024 AND quarter = 1, 0.9840,
region = ‘MEU’ AND year = 2024 AND quarter = 2, 0.9805,
region = ‘MEU’ AND year = 2024 AND quarter = 3, 0.9760,
region = ‘MEU’ AND year = 2024 AND quarter = 4, 0.9715,
region = ‘UK’ AND year = 2024 AND quarter = 1, 0.9795,
region = ‘UK’ AND year = 2024 AND quarter = 2, 0.9825,
region = ‘UK’ AND year = 2024 AND quarter = 3, 0.9795,
region = ‘UK’ AND year = 2024 AND quarter = 4, 0.9660,
region = ‘SEU’ AND year = 2024 AND quarter = 1, 0.9890,
region = ‘SEU’ AND year = 2024 AND quarter = 2, 0.9845,
region = ‘SEU’ AND year = 2024 AND quarter = 3, 0.9820,
region = ‘SEU’ AND year = 2024 AND quarter = 4, 0.9665,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 1, 0.9839,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 2, 0.9823,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 3, 0.9789,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 4, 0.9707,
NULL)
where the parts in brackets is for the total in piviot table, but as can see in the picture. when i change the date the total changes.
Question is there a way to make the total based on the last 4 lines in the calculation field? and also make a conditonal formatiing option that will show red or green based on if below or above the target (as the one i have shows green for total even when under, with rest working).
Hello @darresmi, in regards to the conditional formatting logic, we can definitely make that work! Basically what you would want to do is create a calculated field that will compare the value displayed in the pivot table against the target value. It can be a simple ifelse statement that could return ‘Yes’ and ‘No’ or 1 and 0. Then in your conditonal formatting logic, you can base it on this calculated field rather than the field you are formatting.
if target equals 0.98:
format by = ifelse({value} > 0.98, 'Yes', 'No')
As for the first question, I may need a little more information regarding implementation. Theoretically, you should be able to run the 4 statements that are the most important first. If you move them to the top of the calculated field, they should be utilized in the pivot table if they are true. Let me know if that helps!
Hello @darresmi, can you please follow-up with some more information on your first question so I can help guide you to a solution? If we do not hear back from you in 3 days, I will archive this topic. Thank you!
i have tried to move the 4 most important lines up but doesnt seem to be using line:
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 2, 0.9823, ( set as 4th line) that is needed for ;total;.
if i set it as the first line it will take this into account for all rows and ingnore the 3 other most impronat lines:
region = ‘MEU’ AND year = 2024 AND quarter = 2, 0.9805,
region = ‘UK’ AND year = 2024 AND quarter = 2, 0.9825,
region = ‘SEU’ AND year = 2024 AND quarter = 2, 0.9845,
as all rows in the dataset only contain the 3 regions, is there a better way to force it to do the total based on the calculation field and not as a average or ‘default’ as it says when clicking on the value (have tried all options to no effect in the dropdown)
if you need any mroe information, happliy provide it
as for the contioanl forlatting it works thank you
Hello @darresmi, the issue with your current calculated field is that the last 4 lines of the calculation will never pass as true so they will never be the returned values you will see in the visual. This region = ‘MEU’ AND year = 2024 AND quarter = 1 check will always return true before the check for this portion (region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 1 of the ifelse statement is ever read, so you will never receive 0.9839 as the value. Does that make sense? As soon as a row passes one of the conditional logic checks, it will not be run again.
I think the only way you will be able to visualize the data related to the last 4 lines of the ifelse statement, will be to break this up into 2 different calculated fields. You could add a new field with just this statement:
ifelse(
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 1, 0.9839,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 2, 0.9823,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 3, 0.9789,
(region = ‘UK’ OR region = ‘SEU’ OR region = ‘MEU’) AND year = 2024 AND quarter = 4, 0.9707,
NULL
)
Then apply the total to that field rather than the current calculated field. That will be the most likely way to achieve the result you are wanting.
Hi @darresmi, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!
thank you