Percentage of Total and Filter on 2 Columns

This query shows how to Filter on 2 columns, add 2 measures using AddColumns, and create a measures to calculate the percentage of total for a certain year and country.

DEFINE MEASURE financials[Sales%] =
SUM(financials[ Sales]) /
CALCULATE
( SUM(financials[ Sales]),
  ALLEXCEPT(financials, financials[Year], financials[Country])
) * 100

EVALUATE
ADDCOLUMNS
( SUMMARIZE
  ( FILTER
    ( financials,
      AND
      ( financials[Country] = "Canada",
        financials[Year] = 2014
      )
    ),
    financials[Month Name],
    financials[Month Number]
  ),
  "Sales", FORMAT(CALCULATE(SUM(financials[ Sales])), "#,###"),
  "Sales%", FORMAT(financials[Sales%],"##.##")
)
ORDER BY financials[Month Number]

Explanation:

• The Sales% is defined as Sales divided by the sum of Sales for the year and country currently selected.
• The financial table is filtered on Country = Canada and Year = 2014.
• Then two columns are chosen from this table: Month Name and Month Number.
• Then 2 measures got added: Sales and Sales% (which was defined above)
• Finally it’s ordered on Month Number.

Note: we can’t filter on 3 columns using And function. The maximum argument on And function is two. The And operator (&&) can take more than 2 arguments.

PS.Any one know how to hide the Month Number column?

DAX Studio and DAX Guide

Thanks to Darren Gosbel and others, the Power BI, Power Pivot and SSAS Tabular communities now have a tool to test DAX. It is DAX Studio (http://daxstudio.org/), which is a superb tool. Not only it enables us to test ideas flexibly, but it also enables us to troubleshoot and optimise DAX queries.

With DAX Studio we can:

• Connect to Power BI, a Tabular server and to Power Pivot and run DAX queries on them all.
• Browse the model to understand the tables and columns
• Run DMVs to get the measures, hierarchies, dimensions
• Understand the query plans, Storage Engine and Formula Engine and timing.
• Format our DAX queries
• Capture DAX queries that we run in Power BI (and therefore allowing us to improve their performance)

It is an indispensable tool for anyone working in DAX. With Power BI being ever so popular, the demand for DAX is highly increasing. It was Darren Gosbel who primarily created DAX Studio, with Marco Russo wrote a small part of it.

Marco and Alberto wrote DAX Guide (https://dax.guide/). It is so comprehensive, so detail, so complete. It contains every DAX function in Power BI, in Excel, in Tabular, Azure AS and in SSDT. For every function it describe the syntax, the return values, the parameter, the example, and it describes them in detail. Just look at how it explain the Calculate function. The context transition is there, the filter is there, the order of evaluation is there, the modifier is there. It’s so complete.

Thank you Darren, Marco & Alberto, for creating these two very useful tools for all of us.

PS. All other tools related to DAX, Tabular and indeed other BI tasks are listed here: https://www.sqlbi.com/tools/, e.g. VertiPaq Analyzer, Tabular Editor, BISM Normalizer, DAX Editor, Power Pivot Utilities, DAX Date Template, SSAS Tabular Translator, etc. Note: these tools are written by many people.

Correction, 5/11: DAX Studio was written mostly by Darren Gosbel: http://geekswithblogs.net/darrengosbell/Default.aspx, not Marco or Alberto. My apologies for this mistake. Thanks Marco for your correction.

A few DAX functions

This query shows summarise, addcolumns, filter, union, row and format DAX functions:

UNION
( ADDCOLUMNS
  ( SUMMARIZE
    ( FILTER
      ( financials,
        financials[Product] = "Montana"
      ),
      financials[Country]
    ),
    "Sales", FORMAT(CALCULATE(SUM(financials[ Sales])), "#,###")
  ),
  ROW("Total", "Total", "Sales", FORMAT(SUM(financials[ Sales]), "#,###"))
)

• The financials table is filtered on Product = Montana
• Display just the Country column using Summarize
• Sales column is added using AddColumns functions
• Total row is added using Union function
• Add commas for thousand separator using Format function

Output:
Canada 24,887,655
Germany 23,505,341
France 24,354,172
Mexico 20,949,352
USA 25,029,830
Total 118,726,350

Data source: link
Ref: Whytheq: link, SQLBI: link