Calculating running totals slicer filter and totals : column totals are wrong

[DidierK]

Hi,

I have the following table which tries to calculate running totals for Sales Quantities.

There are slicers on Sales org and material group and a visual filter on the current Year.

Calculating running totals for the rows  work. However, I can't get the totals right (figure 2).

Also, if I select ALL or multiple sales organisations, my calculation gets completely wrong (figure3).

My formula: 

A solution for this would be very appreciated. 

Didier.

[DidierK]

Hi Yolo Zhu,

Thank you for your assistence with this issue. I have created the new measure exactly like you adviced but it did not generate the expected resulted. Below the new figures  for S051 (I cannot figure the logic behind the new figures).

The lowest matrix is the original situation.

Kind regards,

Didier

[Anonymous]

Hi @DidierK 

Which fields in the table correspond to row and column names in your matrix? The logic is to filter out these corresponding values and then calculate them with sumx.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DidierK · Accepted solution]

Hi Yolo Zhu,

I tried the SUMX option with different IN VALUES variables but I couldn't get it working.

However, I managed to find a solution:

ALLSELECTED function: removes the filters

MAX [Calender Year/month] is for calculating the [Year/Month] running total

SELECTEDVALUE on the field [ Newbatch] filters the Newbatch field to one value  (Reman, new, customer repairs) and returns an alternate solution for the totals line-> 'Invoiced Orders Evolution'[NewBatch]

Thanks a lot for your support.

[Anonymous]

Hi @DidierK 

You can create a measure again 

e.g 

Measure =
SUMX (
    FILTER (
        'Invoiced Orders Evolution',
        [Sales org]
            IN VALUES ( 'Invoiced Orders Evolution'[Sales org] )
                && [Calendar Year/Month]
                    IN VALUES ( 'Invoiced Orders Evolution'[Calendar Year/Month] )
    ),
    [RT Sales Quantity]
)

Then put the mesaure to the matrix.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.