I’m trying to see if I can make a histogram using a certain aggregation with a dataset on a different aggregation level.
So I’ve got this dataset that contains Transaction ID and Salesperson:
Dataset #1
I’m trying to create a transactions per Salesperson distribution histogram, so a visual using this data:
I created this data using the following calc:
distinct_count({Transaction_ID},[Salesman])
I create a histogram using that calculation and this is the result:
In the tooltip I have count: 2. So I understand it as count of Transactions in this bin. I would expect to see number of Salespeople that fall in this bin.
So: “There is one person that have 2-3 Transactions in this dataset (David Park)”
Instead of: “There is a bin of Salespeople that have 2-3 transactions. There are 2 rows of data there”
(btw - this is correct in a sense that David Park had 2 Transactions. I just need to know how many people are in this bin and not how many transactions there are)
I created a pivot table in Excel and fed it back to QS. The pivot:
Dataset #2
and the result:
and this is correct. There is one person that falls into this bin.
and I am at loss. I created this histogram using an aggregated calculation (counts Salespeople in bin) and still it shows number as the original aggregation in the dataset (counts Transactions in bin).
I tried adding Distinct count of Salespeople into the tooltip but it crashes the visual.
I also tried Distcountover but no result.
Is the desired result achievable in QS without aggregating the dataset outside?
P.S.
I tried it with some other tools with LOD aggregations in the tool and using the original dataset: