Supply frequency measure...

[Matty]

Hi Team,

Looking for some help to do the following...

Data as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Week[/TH]
[TH]Product[/TH]
[TH]Supply[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Banana[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I want a DAX measure to return the following results:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Supply Frequency[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3.20[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]2.00[/TD]
[/TR]
</tbody>[/TABLE]

Which was computed using standard Excel functions as follows:

=COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">=0")/COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">0")

Logic used:

Per product, establish the range of interest by determining the first and last weeks where supply occurred (note that the first week is always moved on by 1 to 'correct' the range for calculation purposes). In the case of 'Apple', a count of 16 is returned (weeks 5 through to 20). Now, divided this number by the number of times supply has occurred over this same time period. For Apple, 5 is returned (supply occurred in weeks 7, 9, 15, 18 and 20). Finally, divide 16 by 5 to get the desired result: 3.2.

Hope this is clear and someone can assist.

Cheers,

Matty

 

[Matty]

In some ways they are like excel formulas, but CALCULATE is a notable exception.

Maybe think about CALCULATE with two parameters as:

CALCULATE (
     {calculate whatever is written here},
     {but imagine you are in a world as defined here}
)

Therefore you need to know the second parameter before you can work out what the first returns.

That's a good way of thinking about things.

Cheers!

Matty