force 0 when blank with addcolumn summarize excel 2016 dax for more accurate averages

Question

I have one table that has three columns: lisa, customers and activity_type. I want to count the number of rows by customer and activity type and then average them over all customers by activity types. If this were a table in sql, I'd do



SELECT
    lisa,
    customer,
    activity_type,
    average(ct) 
FROM
    (
        SELECT
            lisa,
            customer,
            activity_type,
            CASE
                WHEN
                    s.ct IS NULL 
                THEN
                    0 
                ELSE
                    s.ct 
            END
            ct 
        FROM
            (
                SELECT
                    * 
                FROM
                    (
                        SELECT DISTINCT
                            lisa,
                            customer 
                        FROM
                            TABLE
                    )
,
                    (
                        SELECT DISTINCT
                            activity_type
                    )
            )
            LEFT JOIN
                (
                    SELECT
                        lisa,
                        customer,
                        activity_type,
                        COUNT(*) ct 
                    FROM
                        TABLE 
                    GROUP BY
                        1,
                        2,
                        3
                )
                s 
    )
    s

But it's Dax, which is infinitely harder. I tried:

=
AVERAGEX(
    ADDCOLUMNS(
        CROSSJOIN( VALUES( Query1[customer] ), VALUES( Query1[activity_type] ) ),
        "C", CALCULATE( COUNTA( Query1[engagio_activity_id] ) + 0 )
    ),
    IF( [C] = BLANK(), 0, [C] )
)

and

=
AVERAGEX(
    ADDCOLUMNS(
        SUMMARIZE( Query1[lisa], Query1[activity_type] ),
        "C", CALCULATE( COUNTA( Query1[engagio_activity_id] ) + 0 )
    ),
    IF( [C] = BLANK(), 0, [C] )
)

But try as I might, I still get: Where the blanks are not treated as 0 in the aggregate rows such as the "no" row in the picture above. That roll up amount ignores the blanks when calculating the averages. When I put the cross join into the dax studio, I forced the 0's

So it's a mystery to me where the 0s went.

the only thing that comes to mind is that the CROSSJOIN or the SUMMARIZE in the second one return no rows. You might try to add + 0 after the AVERAGEX() — sergiom, Nov 03 '20 at 09:44

score 0 · Answer 1 · answered Nov 03 '20 at 12:08

0

I think you are complicating

Average=
VAR totalCustomers = COUNTROWS(ALL(Query1[customer])) //this gives you total # of customers

RETURN 
  DIVIDE(COUNT(Query1[engagio_activity_id]) + 0, //the +0 forces the count to always return something
         totalCostumers)

answered Nov 03 '20 at 12:08

Joao Leal

5,533
1
13
23

This didn't work. Instead it divided the number of rows by the number of customers regardless of whether there was a filter on the customer or not. I'm trying to count a row whether it's there or not. – Henrietta Martingale Nov 03 '20 at 14:56
1

Oh, but this worked: divide(countrows(query1),CALCULATE(DISTINCTCOUNT([customer]),all(Query1[activity_type]))) – Henrietta Martingale Nov 03 '20 at 15:19

force 0 when blank with addcolumn summarize excel 2016 dax for more accurate averages

1 Answers1