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

Advertisements