Totals Function for Closing Stock so that is only sums max selected week

[Nix]

I have a data table with closing stock per week for a few weeks that are selected. My data table has a total line but when multiple weeks are selected, I want the total line of closing to only sum the values of the max selected week.

What would be the easiest way to achieve this?

[jonathandienst]

Simplest - assuming your week field is named week (and is numeric):

Sum({<Week = {"$(=Max(Week))"}>} Amount

[Stoyan_Terziev]

Hi Nix,

The in-built total function would always do a generic total. You cannot add any conditions to it.

You can, however, create this total as a separate measure. But that would mean you need to create conditionally the other week-specific measures as separate ones (which means you no longer have the Dimension "week" in the chart).

My advise would be to separate those in two objects. Top one: table with Measure by Week. Bottom one: single-row table with measure Value per Max selected Week.

If you didn't need that measure to be dynamic there would have been a back-end solution to this. If you have the 'Week' field you can create a dimensional table with weeks separated and a week grouping like follows (image below). Inside you can control which weeks contribute to your 'total'

(image is from QV but works just the same in QS)

I hope that helps!

Kind regards,

S.T.

[Nix]

Thank you for the quick reply, but where do I enter that? I don't see an
option to use an expression?
[image: image.png]

Unless it has to do with the access I have..