I’m trying to build a visual with a pivot table.

I have a dataset with multiple messages, some of them with violations. In the table I want to list all the violations and for each violation I want to see the number of messages with that violation, and the percentage of messages with that violation (number of messages with the violation / total number of messages).

The problem comes when calculating the total number of messages, distinct_count(messageId), because in each row of the table, it’s filtered by violation so in every row the divisor is the same as the dividend so the percentage is always 100%.

How can I dynamically create a constant with the total number of messages to use it in all the rows?

You can do distinct count over and partition it by none.

distinctCountOver({messageId},,PRE_AGG)

1 Like