Excel Magic Trick 1439: DAX Measures to Create Cross Tabulated OR & AND Logical Test Sales Total

Download File:
Using Excel 2016, see how to use a DAX Formula with CALCULATE Function, the SUM Function and the IN Operator to create two formulas for adding sales based on Product Groups. This example will work in any version of Excel 2016, even if you do not have Power Pivot. Learn how to create a Cross Tabulated Table to add Sales with Sales Channel Criteria as the Row Header Criteria and Product Group OR Criteria as the Column Header Criteria. See the final Report created with a Data Model Pivot Table. This formula works in both Power BI Desktop and Excel Power Pivot.
Related Videos:
Excel Magic Trick 1439: DAX Formula to Create Cross Tabulated OR & AND Logical Test Sales Total
Excel Magic Trick 1438: Excel Formula to Create Cross Tabulated OR & AND Logical Test Sales Total

[dontown]

This is Sooo! Awesome! DAX & measures w/o needing PowerPivot. Works in Office365 Personal. Wow! Enjoying all your videos.

[mohamedchakroun]

How can we reward you for sharing your khnowledge and mastering excel Hat off

[catherineletendre]

Your videos are awesome. Thanks for sharing!

[jameszhou]

Thanks Mike! This expression is consistent with SQL syntax I learnt recently. However, in the measure, we can also use || OR() to do the same thing, right?

[diegochavez]

Hello from Quito, Ecuador. This message appears when trying to use the Free Style formula:

Esta fórmula no es válida o está incompleta: "The expression is not valid or appears to be incomplete. Please review and correct the expression.
La sintaxis de ';' es incorrecta. (DAX(
IN {"Quad", "Carlota"})
)).
".

Would you help us solve it?

Thanks in advance

[Hanoku]

Required subtotal and counta function combination with filter

[mathijs]

Cant you use an array in dax formula in the filter formula calculate? Typing carlota etc is not flexible.