1

Table contains date and ice-cream sales. Based on Ice cream field highlight the 1st negative number.

I am creating a calculation field using the Index function. Indexing func: IF SUM([Sales]) < 0 THEN INDEX() END

And I am comparing it with the actual index function [![enter image description here][1]][1]below.

Index() = Indexing func I dragged this comparison calc field to color, whenever it matches it should highlight the 1st negative number.

But it is highlighting all the negative numbers in the field.

enter image description here

Improve this question
2
  • Please provide enough code so others can better understand or reproduce the problem. Commented Mar 1, 2024 at 18:28
  • You're only checking whether a value is negative, but you're saying you want to check whether that value is negative and it's the first negative value, so your calculation should really be SUM([Sales]) < 0 AND LOOKUP(SUM([Sales]),-1) > 0. I'm not sure why you're using two indexes, you could just colour where the calculation above wasn't NULL. Commented Mar 22, 2024 at 18:15

1 Answer 1

Reset to default
1

Here is a simple solution that works when you are not using Date as dimension on your view. Otherwise, you can definitely use table calcs - which work but are a bit trickier.

Create a calculated field that holds the Date if it meet s your selection criteria (negative sales in this case) and will be null otherwise. Call the field something like Date_With_Negative_Sales

if [Sales] < 0 then [Date] end

Then you can use Date_With_Negative_Sales on the view as a measure, with either MIN() or MAX() to show the first date or last date with negative sales.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.