Distinct Count by Date in PowerPivot Column

1
votes

I have a client I am importing lots of data from but to narrow it down it pulls in to powerpivot with a date column and a product column. They want to see a summary of how many days there was one item sold ("Solo Sale") two ("Double Sale") three, and four or more.

I am running into trouble due to the fact that they want to summarize the rest of their data by whether the day was a "Solo Sale" or "Double Sale" etc etc. Let's call this this the "Sale Count"

Because I need these "Sale Count" values on the rows of a Pivot Table, I'm trying to build a calculated column in the table that does a distinct count of the products by date but can't quite seem to get there using CALCULATE and/or FILTER. It always seems to give me a total distinct count of the products in the entire table rather than counting by each date.

Any advice on the DAX formula I should be using would be greatly appreciated. New to Power Pivot and I think I'm still thinking a little too excel-centric given there is very little row-logic in Power Pivot.

Thanks in advance!

Edited: Here is the table

PowerPivot Table

I would like the values in the SalesCount column to read 4 for every row with a date 1/1/2016, 1 for 1/2/2016, 2 for 1/3/2016, and 2 for 1/4/2016 (equivalent to the DISTINCTCOUNT of the Products on each day).

Then I would like to use conditional IF statements to replace those numbers with "Solo Sale" "Double Sale" etc so that I can use these values on the rows of a Pivot Table.

1
excelpivot-tablecalculated-columnspowerpivotdax
honestly, I did not understand anything. Can you, please, be more specific about what exactly (with examples) you need to calculate. Help us to help you. – Andrey Morozov
Original post edited to provide more info – A. Reed
I don't know PowerPivot/DAX very well, but took a quick look. Was hoping for something like Excel's COUNTIFS function, but no joy. Another approach would be to do a pivot of a pivot, where the first one gets you your counts and the second one uses those counts as fields. – Doug Glancy
Thanks @DougGlancy, that's what I was thinking my next attempt would have to be. Can you walk me through that? Never gone that route before. Hopefully someone will be able to show me how to do this in DAX though as it would be much cleaner and easier to update – A. Reed
I'm crunched for time now, but comment again in a day or two if the DAX thing isn't happening and I'll mock something up. I agree though the DAX approach is better. – Doug Glancy

1 Answers

1
votes

Try this as your calculated column:

=CALCULATE(
  DISTINCTCOUNT(Table1[Product]),
  ALLEXCEPT(Table1, Table1[Date])
 )