Calculating Sum and Divide it by Unique Count of the Month for Rolling 4 or 12 Months

[Triconds]

Hi everyone,

I am still new to Power BI and I have tried many different approaches but none seem to come close to what I need. I have this set of data:

The goal is to get the "Average Life" of each product category (in Category Description) in a 4 or 12-month rolling average. The data has multiple Sites, Drills, Category, Size, and Type. Each date might have more than 1 entry, I would then need to use 5 slicers (Site, Drill, Category, Size, and Type) to filter the data accordingly, but the drill might not be filtered at times (displaying the average life across the selected site).

This was done by adding up all the metres for the period (4-month for ROTARY BIT and HAMMER BIT and 12-month for everything else) and dividing that sum by the distinct count of the serial number (which represents the number of items discarded, giving an average of each product's life).

Would someone please be able to assist?

[v-yuta-msft]

Hi Triconds,

"The goal is to get the "Average Life" of each product category (in Category Description) in a 4 or 12-month rolling average. The data has multiple Sites, Drills, Category, Size, and Type. Each date might have more than 1 entry, I would then need to use 5 slicers (Site, Drill, Category, Size, and Type) to filter the data accordingly, but the drill might not be filtered at times (displaying the average life across the selected site)."

<--- Could you give your expected result with this logic or share the current dax formula you are using?

Regards,

Jimmy Tao

[Triconds]

Hi Jimmy,

I shall try my best to explain this as clear as possible due to the complexity of the calculation that I am trying to achieve.

Let's take the following data for example:

In the data, the sum of metres is 12,997 with 25 unique serial numbers. Out of the 25 unique serial numbers, grouped by the category and size, these are the count of the unique serial numbers: 

• 1 Cushion Sub
• 4 Deck Bush Rollers (3 x size 7 5/8 and 1 x size 7)
• 8 Drill Pipes (6 x size 7 5/8 and 2 x size 7)
• 3 Rotary Bits (1 x size 9 7/8 and 2 x size 9)
• 4 Rotary Bit Subs (3 x size 7 5/8 and 1 x size 7)
• 2 Saver Subs (1 x size 7 5/8 and 1 x size 7)
• 1 Shock Sub
• 2 Top Subs

Within the report, we would have 5 slicers as follows:

• Site:
• Drill ID:
• Category:
• Size:
• Type:

The output that I expect without selecting any option on the slicer would be 12,997 / 25 = 519.9 (rounded to 1 decimal figure).

With the following slicer selections:

• Site:
• Drill ID:
• Category: Rotary Bits
• Size:
• Type:

Expected Result: 1,315.9 (Sum of metres for all rotary bits) / 3 (count of the unique serial number of rotary bits) = 438.6

Drilling down further:

• Site:
• Drill ID:
• Category: Rotary Bits
• Size: 9
• Type:

Expected Result: 809.3 / 2 = 404.7

Another example:

• Site:
• Drill ID:
• Category: Drill Pipe
• Size:
• Type:

Expected Result: 3698 / 8 = 462.3

Drilling further down:

• Site: Hope Downs 1
• Drill ID:
• Category: Drill Pipe
• Size:
• Type:

Expected Result: 1250.4 / 4 = 312.6

But again this only gets more complicated when the entries would have different months. As mentioned previously, the accumulated metres for Rotary Bit and Hammer Bit would be for the previous 4 months while it would be for the previous 12 months for everything else.

I hope the above is clear. Please do let me know if you require further clarification. The total data contains over 600,000 rows and processing time does become significant so any aspect to improve this would definitely help too.

Regards,

Diovian Luwandri