Please help me calculate the correct average

Question & Answer
, , , , ,
1

here is what my data looks like:

ROW_TYPE DATE BRANCH DOWNTIME_IN_HOURS DOWNTIME_REASON ORDER_NUMBER
BRANCH_STATUS 2024-11-01 A 4 NO DRIVER NULL
BRANCH_STATUS 2024-11-01 A 2 INTERNET DOWN NULL
BRANCH_STATUS 2024-11-01 B 1 NO DRIVER NULL
BRANCH_STATUS 2024-11-02 B 2 SERVER DOWN NULL
BRANCH_STATUS 2024-11-02 D 1 SERVER DOWN NULL
ORDERS 2024-11-01 A NULL NULL 111111
ORDERS 2024-11-01 B NULL NULL 12445
ORDERS 2024-11-01 C NULL NULL 765443
ORDERS 2024-11-02 A NULL NULL 1111113
ORDERS 2024-11-02 B NULL NULL 3325
ORDERS 2024-11-02 C NULL NULL 785443564

Notice there are 4 branches in total, 3 of them posted downtime and 3 of them posted sales (orders). also the selected period is 2 days. this is important to calculate the average

I’m trying to build a pivot table similar to this:

Downtime Reason Affected Branches Total Downtime Avg. Downtime per Affected Branches Overall Avg.
NO DRIVER 2 5hr. 1.25hr. 0.625hr.
INTERNET DOWN 1 2hr. 1hr. 0.25hr.
SERVER DOWN 2 3hr. 0.75hr. 0.375hr.
Overall 3 10hr. 1.6hr. 1.25hr.

In general, the average is the (sum of downtime hours) /( affected branches) / (number of days)

My problem is that I could not find the correct number of branches to calculate the "Overall Average. "

distinctCountOver(BRANCH,[ ],pre_agg)

this gets the correct total number of branches (4) but I can’t mix it with sumOver(DOWNTIME,[ DOWNTIME_REASON],pre_agg). I get an error message about using the same aggregation column in the visual.

2

Hi @Ali_B
try distinctCountOver(BRANCH,[{DOWNTIME_REASON}],PRE_AGG)
BR

3

Hi!

The reason you are getting this error is because the distinctCountOver and sumOver functions return aggregated results and you have to keep this in mind when combining.

I would suggest splitting up this calculation to simplify it:

  • Use a calculated field to get the total downtime: sumOver({DOWNTIME_IN_HOURS}, [{DOWNTIME_REASON}], PRE_AGG) and another to get the distinct branches: distinctCountOver({BRANCH}, [{DOWNTIME_REASON}], PRE_AGG)
  • Get the average downtime per affected branch by total downtime / distinct branches
  • Overall downtime should be similar to sumOver({DOWNTIME_IN_HOURS}, [], PRE_AGG)
  • Overall branch count should be distinctCountOver({BRANCH}, [], PRE_AGG)

So your final calculation should be:

{Overall Total Downtime} / {Overall Distinct Branch Count} / {# of Days} (however you want to calculate the granularity based on your date)

Feel free to follow up for further help!

Sincerely,

Arnav

4

This will return the number of affected branches per a specific reason. if a branch did not post downtime, it will not be counted

5

Hi Ali,

That should not be the case for the solution that I have posted, as it considers branches that did not post downtime as well!

6

That reply was to ErikG,

Your solution does not seem to work either, using sumOver(DOWNTIME_IN_HOURS,[ ],pre_agg) will return the total downtime(10hr) repeated for every reason row in the table, but what I want is the total downtime per reason divided by the total number of branches (4)

7

image
image1069×236 7.34 KB

Total downtime per reason divided by the total number of branches divided by the number of days.

however, it does not give the correct overall average in the total row, only on the reason level.

image
image478×347 20.4 KB

8

@Ali_B can you filter out the null downtimes and check if the overall average will appear?

9

Hi @Ali_B,
It’s been awhile since we last heard from you. Did you have any additional questions or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

10

Hi @Ali_B,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!