Why different totals?

[Anonymous]

Can anyone explain why these two formulas give different totals?    What is more confusing to me is that the 2nd measure (which gives the correct total) is actually referenced in the first measure...but the first measure not only gives wrong totals per month, but also wrong grand total.     Confused.  

Distinct Count of Zero Value Shipments = CALCULATE( DISTINCTCOUNT( 'Flu_Shipped Orders'[Order]),'Flu_Shipped Orders'[Zero or Revenue Shipment]="$0 Value Shipments")

Correct Totals Zero = SUMX(VALUES('Date Table'[Month]),[Distinct Count of Zero Value Shipments])

[mahoneypat · Accepted solution]

Note that your first measure total is less than the second.  That's because Totals are not Totals in Power BI.  It would be better to call them All Rows in that unlike each of the rows in the table that filters the data first, the "Total" does not have that filter, so all rows are included in the DISTINCTCOUNT() calculation.  Since you are more likely to have duplicates when more rows are included, the result is less.

The second measure is a common pattern you'll use and whole blogs have been written about it.  With that pattern, you create a virtual table of all the distinct values for each row and their distinctcounts, which is then summed up to get the correct total.

https://powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I am not sure if the picture can be seen, that was supposed to be in my original post.   

But here it is, below