How to get rolling Wow values from the tall format data set

[Anonymous]

Item	Week	Driver	Score	Resp
1	551	D0	6	98
1	551	D1	6.87	189
1	551	D2	6.67	63
1	551	D3	5.3	70
1	552	D0	6.92	182
1	552	D1	7.24	133
1	552	D2	4.57	49
1	552	D3	6.25	70
3	551	D0	7.8	196
3	551	D1	7.32	77
3	551	D2	4.97	105
3	551	D3	5.07	161
3	552	D0	5.31	112
3	552	D1	11.3	70
3	552	D2	5.83	42
3	552	D3	5.55	133
4	551	D0	4.4	70
4	551	D1	5.04	91
4	551	D2	8.27	469
4	551	D3	3.1	70
4	552	D0	3.86	49
4	552	D1	15	35
4	552	D2	8.71	133
4	552	D3	6.21	49
7	551	D0	8.63	119
7	551	D1	10.89	7
7	551	D2	4.57	49
7	551	D3	10.69	35
7	552	D0	6	98
7	552	D1	6.87	189
7	552	D2	6.67	63
7	552	D3	5.3	70

Hi everyone,

I want  to calculate the WoW values of the driver at item level.

The values should be in a crosstab as below:

item	D0	WoW	D1	WoW	D2	WoW	D3	WoW
1	9.1		8.76		8.1		9.4
3	9.3		9.04		8.8		9.6
4	9.4		9.18		9.15		9.7
7	9.6		9.46		9.85		10.2

If I select week 552 from the filter, then column D0..D3 in table 2 should show the weighted sum of score and responses and the WoW columns should show the weighted sum of week 551.

The challenge here is that if I select week 553 and 554, then I am expecting the WoW column to show the weighted mean of 551 and 552. My data is for 52 weeks. Similarly, selecting 3 consecutive weeks should give me the weighted mean of previous 3 weeks corresponding to the item and driver(D0..D3) and so on.

Can somebody please help me in this.

@Anonymous @PowBi @parry2k @jdbuchanan71 @Greg_Deckler @Cmcmahan 

[v-juanli-msft]

Hi @Anonymous 

Could you tell how to calculate the column D0..D3 in table 2 which should show the weighted sum of score and responses If I select week 552 from the filter?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @v-juanli-msft,

Thanks for replying

The method to calculate weighted mean is through a DAX query.

The query is: SUMX(table, score*responses)/sum(response).

This is the way to calculate D0..D3 for each item and the D0-WoW..D3-Wow is also caluclated the same way, just for the previous weeks.

Is this what you were asking?

Thanks

[Cmcmahan]

I honestly have no idea where to even start so we can talk using a shared dictionary.  The data you shared is VERY helpful as a starting point, but I can't seem to replicate the 2nd table you shared that shows your current results.  I have no idea how you get to a value of 9.1 for Item 1, D0.  Or any of the other numbers in that table using the data provided. 

In addition, I have no idea what a "WoW Value" is, or how to calculate it.   And that's before we add in the fact that you want a rolling WoW Value.  You seem to be wanting some sort of weighted average, but how do you determine the weight?  You say that you want WoW for previous weeks, but how many previous weeks? Should D0-WoW have the same value as D0, but for the previous week or something?

I've attached the .pbix file I've played with to try and replicate your results, so you can see where I've gotten stuck.  Hopefully if you can explain what I'm missing, we can get you your WoW value.

[Anonymous]

Hello, @Cmcmahan  Thanks for your support,

Here, Answer to your first question is that we are calculating the 9.1 value as

SUMX(Score * Resp) / SUM(Resp) just like weighted scores which is lets just say Value XYZ. 9.1 is not the correct value, they are just dummy values. 

The second column has the values with WoW i.e. Week over Week change. 

WoW is calculated as the XYZ of current week(or the week that I select) - the previous week XYZ value.

The problem that I am facing is that if I select multiple weeks, I should have WoW value as the XYZ from the current selected weeks and subtract it from XYZ from previous same number of weeks.

Let's say I calculate the XYZ for an item at D0 column for week 552 (which is selected from the page level filter), the WoW column should contain the value for week 551 at the same item. 

But, if I select week 553 and 554 for the same procedure, the D0 would have the values for 553 and 554 but the WoW should contain the values for 551 and 552. 

Similarly, the number of weeks selected, D0 column would have the weighted mean(XYZ) for those months and the WoW column would show the values for the same number of weeks(previous to the minimum week selected).

The way you have calculated the values are the correct way, but the WoW column should have the values for previous weeks.

So, How will be I able to solve it from DAX expressions.

[Cmcmahan · Accepted solution]

Ahh, now THIS makes sense.  So assuming you have a measure for the current timeframe [Weighted Mean] like this:

Weighted Mean = SUMX('Table', [Score] * [Resp]) / SUM('Table'[Resp])

The basic form of the DAX for a single week's WoW value would be:

WoW Mean = [Weighted Mean] - CALCULATE( [Weighted Mean], FILTER( ALL('Table'[Week]), 'Table'[Week] = SELECTEDVALUE('Table'[Week])-1))

Since you just want the difference between the current [Weighted Mean] and the [Weighted Mean] with a different set of weeks, you can do that with CALCULATE syntax to change the context for part of the measure. 

However, you've gone a step further and added the requirement that if there are multiple weeks available via filter/slicer, then it should use the same number of previous weeks.  Still doable, we just need to get trickier in how we select the previous timeframe.

WoW Mean (Multiweek) =
// VAR numWeeks = DISTINCTCOUNT('Table'[Week]) // Selected weeks may not necessarily be continuous, or there may be a week with no data
VAR numWeeks =  // Better formula for figuring out the timespan, though will still give weird results if you skip a week in slicer (e.g. Week 5,6,8 will compare data from weeks 5,6, and 8 to data from weeks 1-4)
    MAX ( 'Table'[Week] ) - MIN ( 'Table'[Week] ) + 1
RETURN
    [Weighted Mean]
        - CALCULATE (
            [Weighted Mean],
            FILTER (
                ALL ( 'Table'[Week] ),
                'Table'[Week]
                    IN GENERATESERIES ( MIN ( 'Table'[Week] ) - numWeeks, MIN ( 'Table'[Week] ) - 1 )
            )
        )

I've attached a .pbix that I used.  I added random [Score] data for weeks 53/54, but kept [Resp] the same due to laziness.

Note that there are many ways to do this filter instead of using IN GENERATESERIES style syntax. 

My first thought was to use IN ADDCOLUMNS(VALUES('Table'[Week]), "Prev Week", 'Table'[Week]-numWeeks)) style syntax, but I think I GENERATESERIES is an easier/clearer way to do it. This version also had the same issue of handling weeks with no data/filtered out data poorly.

You could also use 'Table'[Week] >= MIN('Table'[Week])-numWeeks && 'Table'[Week] < MIN('Table'[Week]) style syntax, which may be easier to update, but (I think) is harder to read.   

The point is to get that FILTER statement working for the previous time period's data. Once you do that to change the context properly, the rest should fall into place.

[Anonymous]

Hi @Cmcmahan 

Thanks for posting such an ellaborating way to achieve the result. Earlier, I was trying to use this same DAX expression but with && but it does not works as it gives the result in boolean equation and not whole numbers, therefore not giving the result.

If any suggestion on how to use with &&, please reply. But, the problem is solved with Generateseries. It worked like a charm.

Thanks again for your support.

Dhruv Vats

[Cmcmahan]

No problem. It was an interesting problem to solve.  We do it all for the kudos and accepted replies.

What part were you using the && in? The filter?

As far as using (value > MIN(other value) && (value < MAX(last value)) style syntax for filtering, you would probably need to wrap that into a FILTER(table, <condition>) setup to work.