Advanced use of tables - from "The Definititive Guide to DAX Second Edition"

[edhans]

I'm reviewing some concepts in this book and I'm missing something in one of the examples. It is on p67 if you have the book. Here is the issue I am hitting:

The following table formula generates a table of only categories and subcategories where the sales are greater than twice the average sales:

BestCategories = 
VAR Subcategories =
    ALL ( 'Product'[Category], 'Product'[Subcategory] )
VAR AverageSales =
    AVERAGEX (
        Subcategories,
        SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
    )
VAR TopCategories =
    FILTER (
        Subcategories,
        VAR SalesOfCategory =
            SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
        RETURN
            SalesOfCategory >= AverageSales * 2
    )
RETURN
    TopCategories

The problem is I am not understanding how RELATEDTABLE is working here. How is the Sales table related to the table created in the Subcategories variable?

If I create a table this way:

BestCategory Analysis = ALL('Product'[Category],'Product'[Subcategory])

I get a list of all categories and subcategories. Simple enough.

Then if I create the following measure....

Total Sales = 
    SUMX(
        RELATEDTABLE(Sales),
        Sales[Unit Price] * Sales[Quantity]
    )

I get the same value for all rows in the table when using the Table visual. Same thing if I use a calculated column. The 

So why is the relationship working in the big table creation in the first table formula above? See the "F 03 07.pbix" file in the sample data for the book.

[v-lid-msft]

Hi @edhans ,

Based on my understand, For the FILTER function in TopCategories,  Boolean expression

VAR SalesOfCategory =
            SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
        RETURN
            SalesOfCategory >= AverageSales * 2

well be evaluated for each row of the table Subcategories, such as for the row "Category = Computers and Subcategory = Laptops", it contain sub-table of product , this sub-table contain multi distinct ProductKeys.

Because Table Production and Table Sales has relationship between ProductionKey, so use RELATEDTABLE ( Sales ) can get the related sub-table of sales based on the ProductionKey, so we can compute the total sales using SUMX for each row of Subcategories and return a boolen value to filter the Subcategories tables

Best regards,

[Anonymous]

Hi 

I think what you are missing is  the following:

the formula can also ve written as Follow:

FILTER (
         ALL ( 'Product'[Category], 'Product'[Subcategory] ),
        VAR SalesOfCategory =
            SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
        RETURN
            SalesOfCategory >= AverageSales * 2
    )

Which is why the related table is working, since it is using the Product table relationship to the Sales table.

On the same that you show that did not work, you are missing the Product table:

Total Sales = 
    SUMX(
        RELATEDTABLE(Sales),
        Sales[Unit Price] * Sales[Quantity]
    )

It is only calculating the SUMX for all the rows in Sales since you are adding the other table.

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[edhans]

Thanks @Anonymous . I did leave the product table out. But still struggling to get a measure to return the total sales. My issue here is trying to work through building this in steps before consolidating into one nice table as the book shows. I'm trying to build step by step.

Your version is also returning a table though, not the total sales or average sales. That is a separate issue though as my leaving out the product table in the ALL() function was why I was getting the same result. 

[v-lid-msft]

Hi @edhans ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,