Best Of

@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like:

CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END

Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at the Beast Mode level should you want)

This sounds like a good option to use a Histogram (if you don't need that much granular control over the partitions). If you need custom partitions, I'd strongly advise doing a View of your dataset that already aggregates the total time at the DIV_OrderNum level and then graph based on this with a simple ETL to bucket them. If you can't or really don't want to for some reason, I'd use FIXED instead of OVER, something like:

CASE
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'a)0-50'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'b)50-100'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'c)100-150'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'd)150-200'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'e)200-250'
WHEN SUM(SUM(`Total_Hours_Worked`) FIXED(BY `DIV_Order_Num`)) <50 THEN 'f)250-300'
ELSE 'g)>300' END

Then make sure your card is set to sorted by this new field only and that the what will show is a

COUNT(DISTINCT `DIV_Order_Num`)

Again I'd preferred to go with a GroupBy view of the table that aggregates things at the DIV_Order_Num instead, to make calculations simpler for anyone that might take a look at this later.

You should be able to achieve this using FIXED functions in the Beast Mode. Here is the documentation for them:

https://domo-support.domo.com/s/article/4408174643607?language=en_US

You'd want to use the FILTER option of the FIXED function to make sure your monthly NPS isn't affected by the fields you specify.

@art_in_sky I'd suggest using the Unpivot Tile in MagicETL to restructure your data into 3 columns: Chapter, Date/Month, and value. Then you can create a card with Date/Month as your dimension, Value as your measure, and Value as your metric.