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

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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 6, 2013 3:16 PM by Marco Russo (SQLBI)
Filed under: , ,

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
New Comments to this post are disabled

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

Privacy Statement