The solution requires you to first generate a table containing the headers, i.e. CustomerContinents and Categories, and then, for each of the rows of this header table, perform the calculation in a second subquery that actually executes the computation.
In order to make it work, you have to make sure that column names do not conflict, this is the reason why you need to rename Customer[Continent] to [CustomerContinent]. Of course, using SELECTCOLUMNS in DAX 2015 would help making the code easier but at that point you might use GROUPBY (both these functions are new in DAX 2015).
EVALUATE
GENERATE (
SUMMARIZE (
SUMMARIZE (
Customer,
Customer[Children At Home],
Customer[Total Children],
Customer[Continent],
"Children Status", IF (
Customer[Children At Home] = Customer[Total Children],
"All at home",
"Children Left"
),
"CustomerContinent", Customer[Continent]
),
[Children Status],
[CustomerContinent]
),
CALCULATETABLE (
ROW ( "Amount", [Sales Amount], "Count", CALCULATE ( COUNTROWS ( Customer ) ) ),
CALCULATETABLE (
SUMMARIZE (
Customer,
Customer[Children At Home],
Customer[Total Children],
Customer[Continent]
),
FILTER (
ALLSELECTED ( Customer ),
AND (
IF (
Customer[Children At Home] = Customer[Total Children],
"All at home",
"Children Left"
) = [Children Status],
Customer[Continent] = [CustomerContinent]
)
)
)
)
)
ORDER BY
[CustomerContinent],
[Children Status]
DAX Book References
You can find more information about GROUPBY in The Definitive Guide to DAX:
- Chapter 9 – Advanced table functions
- Section “Understanding grouping/joining functions – Using GROUPBY”