I’m trying to calculate the average # of transactions (ie - total number of invoices divided by the total number of accounts) to show on a KPI widget in a dashboard. Does anyone have any tips for pulling in the number of customers? I am stumped.
Hi LGagne,
If you’re still stumped, you can write an expression in designer that assigns a 1 to a unique customer and 0 if it’s a duplicate. Then, if you assign the expression as a measure, it should give you the customers with transactions for a defined period. This is assuming the customer record is in your transactional file. Below is the syntax I use in the expression:
case when row_number() over (partition by “Your Customer Record” order by “Your Customer Record”) = 1 then 1 else 0 end
I hope this helps