Build histogram showing count of unique records across different sales buckets

[stevenbrisley]

Hello!

I've come close to solving this challenge but have yet to find a fully working solution. I'm hopeful someone else may have more luck than I have. 

Goal: Build a histogram (column chart) where the values (on the y-axis) are the count of unique Product_IDs across buckets. The buckets, meanwhile, are defined based on the total sales for each Product_ID.  This matters because there are more than one row for each Product_ID (since each record is a transaction).  In addition, I need a solution that works in conjunction with the slicers -- which woudl be used to filter the records (transactions) that are included in the considered dataset. 

Example data (simplified as there are other fields):

Product_ID	Sales
0001A	$20
0001B	$5
0002A	$15
0003A	$8
0004A	$12
0005A	($2)
0001B	$15
0002A	$10

Based on this, the distinct count of Product_ID would be 6. 

Then you'd get the following histogram inputs based on these three buckets:

Buckets (Sum of Sales $)	Distinct Count
<=5	2
5 < x <=20	3
>20	1

How can I go about creating a measure that would calculate the values in the second column of the table above?

Thanks for your help!

Steven

[Anonymous]

Hi!

Well, for your case, i've created an aggregated table with SUMMARIZE DAX FUNCTION,

if you have the "table_data" with "product_ID" and "Sales" columns, the sintax for aggregated table is:

table_data_AGGREGATED = SUMMARIZE(table_data,table_data[Product_ID],"SUM OF SALES",SUM(table_data[Sales]))

then, i've created a calculate column for your logic:

CATEGORY = IF('table_data_AGGREGATED'[SUM OF SALES]<=5,  "<=5" ,
IF('table_data_AGGREGATED'[SUM OF SALES]<=20 , "5 < x <=20" , ">20"))

The final result:

[stevenbrisley]

Thank you! Working with this now. Two follow-up questions:

1. Can I create the aggregated table as a Measure or is there another approach I need to do so?

2. Will using the calculated column method allow for the result to change as I filter the data? For instance, if my data set is for the past year, but I decide I only want to look at the past 30 days and use a slicer to limit the records, will the calculated column "re-calculate" or will it include data from the full year because that's what was used when the column was created?