Forum Discussion
Pivot table summarization by Average calculates incorrect Total Averages.
Blanks are ignored in DAX. If 0 is represented in your data by a null / blank then that row will not be considered in the average in the numerator or denominator.
Blanks are ignored in DAX. If 0 is represented in your data by a null / blank then that row will not be considered in the average in the numerator or denominator.
I imported the data from a stored proc and bingo! Good numbers.
I hate excel...
But killing excel based processes has given me a wonderful career here in Silicon Valley.
I started with SQL Server v 6.21A, just to date myself.
Again, a huge tanks to all.
This thing rocks!
jeffreykeryk, glad to help. Hopefully I can help you a little more. With a SQL background, Power BI is easier than with an Excel background.
DAX is syntactically very far from SQL, but semantically quite close. Thinking in terms of relational abstractions will be much more useful at grokking DAX and the PBI data model than in terms of Excel abstractions. In Excel, the fundamental unit is the cell, and everything is evaluated in order based on what can become a very complicated dependency tree.
In DAX / Tabular, the fundamental unit is a table or a field. The (active) relationships in a model act pretty much as if everything you do is written with a left join between the tables in the relationship. In N:1 relationships, the N is the left side of the join. The new bidirectional relationships in PBI more closely mimic inner joins.
Like we saw above, AVERAGE() is pretty much identical semantically to the equivalent in SQL; nulls are excluded from numerator and denominator. DAX plays a little bit faster and looser with types than SQL (e.g. blanks can be implicitly cast to other data types). Overall though, you're operating on fields and tables, like in SQL. Your WHERE clauses just tend to be implicit in the context coming in from visual axis and category label, and the majority of your queries end up being measures which are returning scalar values under many different WHERE clauses (each category, axis, or row label).