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
Reblogged this on MS Excel | Power Pivot | DAX | SSIS |SQL.
Comment by selfservicebi — 4 November 2018 @ 5:27 pm |