I was wondering if I really need the DISTINCT over a SUMMARIZE call as the latter would already return unique combinations of order date and order number. Also, is there a better way to calculate the total number of orders for each day for which the sales amount > 0 ?
Create a Calendar Table and build a relationship from the Order date column of your Sales Table to the Date column of your Calendar Table. Write calculated column formlas to extract various time dimensions such as Year, Month, Quarter
Drag time dimensions from the Calendar Table.
Write these measures
Total sales = SUM(Sales[Sales amount])
Orders which recorded sales = SUMX(FILTER(SUMMARIZE(Sales,Sales[Order Number],Calendar[Date],"Sale",[Total Sales]),[Sale]>0),[Sale])
@Greg_Deckler Yes, that's what I thought. I removed DISTINCT from the measure definition and the numbers seem to match. I am just trying to think of a case where I would need a DISTINCT over SUMMARIZE. It's impossible for me to test all possible combinations considering the dimensions I have in my dataset. In your experience, was there ever a case where you had to apply DISTINCT over a SUMMARIZE statement ? I am yet to do a performance comparison between both the measures.
No on DISTINCT with SUMMARIZE, because when you SUMMARIZE, whatever your are grouping on will be distinct. DISTINCT will make sure that entire rows are distinct in a table versus just a particular column, but the fact that you are using SUMMARIZE ensures that the table rows are distinct, it's built into the grouping.