THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Group Sales by Age of Customers #dax #powerpivot #tabular

I published an article describing how to implement the grouping of sales transactions by age of customer at the moment of the transaction by using PowerPivot or Analysis Services. The same pattern can be used also for any kind of banding operation, this specific case is useful also to recycle the formula that gets the exact age of the customer for each transaction.

An interesting point is related to performance optimization. The technique is based on adding a calculated column in a table that might contain millions of transactions. This is less expensive than adding a column that contains a foreign key and then a relationship between a table containing group definitions and the transactions table. Every relationship is expensive and generates additional structures (you can see more files in the Analysis Services database, too). Adding one or two columns that have a low number of distinct values (10-15 rows) usually has a lower memory cost than creating a relationship with another table. The article also contains PowerPivot examples for both Excel 2010 and Excel 2013.

If only I could decouple attribute visualization from physical structure, I would put these “degenerate dimensions” in a separate folder, because in this way such attribute will be included in attributes belonging to the fact table, which might not be so clear in presenting data. However, I understand that such a decoupling could make live very hard to DAX clients (but probably for MDX it could be not a big issue).

Published Monday, May 06, 2013 3:16 PM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dan said:

Hi Marco,

i have the next problem in DAX: one fact table Sales and one dim table Products with some properties (color,size,..). How can I calculate how many invoices are with all the products green/invoice (invoices that contains only products=green) ?  Thank you!

June 4, 2013 11:14 AM
 

Marco Russo (SQLBI) said:

It should something like

CALCULATE ( COUNTROWS ( Sales ), Products[color] = "Green" )

June 5, 2013 2:27 AM
 

Dan said:

Thank you for your answer, but I want the number of invoices in witch all the product are green.

It's the Adventure Works sample database with FactResellerSales and DimProduct.

in sql would be:

SELECT COUNT(SalesOrderNumber) FROM

(

SELECT SUM(OrderQuantity) C,SUM(CASE WHEN dbo.DimProduct.Color='Black' THEN OrderQuantity ELSE 0 END) AS QtyBlack, dbo.FactResellerSales.SalesOrderNumber

FROM dbo.FactResellerSales

INNER JOIN dbo.DimProduct ON dbo.FactResellerSales.ProductKey = dbo.DimProduct.ProductKey

GROUP BY SalesOrderNumber

HAVING SUM(OrderQuantity)=SUM(CASE WHEN dbo.DimProduct.Color='Black' THEN OrderQuantity ELSE 0 END)

)x

but I dont know how to translate it to DAX.

June 5, 2013 11:31 AM
 

Marco Russo (SQLBI) said:

You need the following calculation:

   COUNTROWS (

       FILTER (

           ADDCOLUMNS (

               SUMMARIZE (

                   'Internet Sales',

                   'Internet Sales'[Sales Order Number]

               ),

               "Total", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) ),

               "Color",

                   CALCULATE (

                       SUM ( 'Internet Sales'[Sales Amount] ),

                       Product[Color] = "Black"

                   )

           ),

           [Total] = [Color]

       )

   )

June 13, 2013 9:05 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement