It feels to me like the issue is one of adjusting the "Compute using" and "Restarting every"
for each of your calculations.
I mocked up some data and mocked up a workbook. Not sure if they correctly match
your setup, but please adjust them and repost, if needed.
Below is how the Table Calculation for MoM was set up:
212544filt.twbx 36.5 KB
Thanks for the detail! I adjusted my worksheet to mirror what you had with the table calculation on (MoM) but as you can see the MoM and KPO Mark Sustain are null for the first group in Sponsor (dimension). It is defaulting to mirror Dimension value B again as before. This hasn't shown up in the 20+ other various worksheets I've done with the same data set.
One thought (admittedly a grasping-at-straws one): Could the ZN LOOKUP on (MoM) be negating the first entry as there is the -1 command? That is to say, since I have a defined time range (although the data extends farther back) could it be voiding out Dimension Value A since it doesn't have a value before it? Nothing comes before value A.
The ZN LOOKUP calculation is targeted to Delivery Performance, but I'm wondering if it is linking it to Sponsor somehow?
Yes, the nulls are on account of the first entry having nothing to lookup.
It looks like the current Addressing is setting the whole first Sponsor to Null,
but really only the first month should be null.
I think the next step will be to make sure that all four of the table calculation pills
(MoM, KPO Mark Sustain, and KPO Avg Sustain in both places) have the
identical Addressing and Partitioning. Then if you would, please post screenshots
of both the Table Calculation window and the Advanced window (as shown in
my previous message) for one of them, say KPO Avg Sustain.
Another question, should the KPO Avg Sustain be the same for all lines
of the same Sponsor?
The KPO AVG Sustain would be the same in each sponsor group as an aggregate of the KPO Mark Sustain values over the date range. So it would be a running average - I don't require a distinct calculation for every month.
For the table calculation portion on each pill - I did update each and made sure the Addressing was ordered correctly. There was an interesting outcome.
Screenshot 1 shows the 3 pills updated that are in the Rows Shelf. It finally populates MoM and KPO Mark Sustain! Of course it's showing null for the first month, but progress! KPO AVG Sustain still doesn't populate, and I noticed the KPO AVG Sustain column on the last right is identical in each Sponsor group.
Screenshot 2 shows the 3 pills updated that are in the Rows Shelf and the one in the Label section as well. This one shows a corrected KPO AVG Sustain in the far right column.
I really appreciate your perspective and help on this!
Hmm. Your addressing looks exactly like what I have for my KPO Avg.
Will need to think some more as to why the 1st Sponsor has all nulls.
With regards to the first month's MoM and KPO Mark being null, I think
that is always going to be the case on account of that being the first data point
in the view. If you do want it to populate based on a value not in the view
(i.e. December of the previous year), that will take a bit more work.
I did notice a discrepancy: in the view, you are displaying the SUM(Delivery Performance),
but in MoM, you are using AVG(Delivery Performance), and so the numbers
on the visualization give the appearance of being inconsistent.
It may have been overkill with the Addressing.
You may want to try different "Compute usings"
for [KPO Avg]. I tried using simply just [Date],
and it seemed like the averages stayed the same.
212544filt3.twbx 37.9 KB
That seems to have resolved it for me! Had to do a few additional tweaks, but it finally allowed the segmentation I needed. Thanks!