Sometimes it makes good sense to apply break on measures.

Let’s build a query based on the eFashion universe that contains product Lines, product Promotion Cost, Store Name, and Sales Revenue. Promotion cost is money spent for advertising a product, it is not directly related to stores. So the report is showing the same product promotion cost for different stores.

Technically speaking, the queries from two contexts are merged in the table. The first query is from “Promotion” context, it contains [Lines] and [Promotion Cost USD]. Second query is from “Shop facts” context and it contains [Lines], [Store name], and [Sales revenue]. The queries are merged by [Lines].

This looks not nice because it might seem that the same amount is spent for each store for a product. The readability can be improved by defining break on the measure.

Add break on Promotion Cost, Remove break header and footer rows, Show table header, Center values across break

However defining the break only on the measure is a wrong way. You can get very unexpected results. For example, when the promotion cost will be same for a couple of products, or when a calculation is used (see a puzzle in the end of the post). It would be more correct to define a break by product lines and then the break by promotion cost.

Remove break from “Promotion Cost”, Add break for the column “Lines”, and then again Add break for the column “Promption Cost”, Remove break header and footer rows, Show table header, Center values across break

Ok, that’s it. The point it is if you want to have a break for a measure, you have to define break for the corresponding dimension.

The next question is what if we do not want to see the break by Lines as a break, i.e. we want the product line name in each row. A simple solution is to duplicate column Lines, and “hide” the one with the break.

Add another column “Lines”, change expression for the first column to =[Lines]+””, Hide the column “Lines”

This does not look neat because you cannot really hide the column. But you can build nice appearance using alert that replaces value of the cell with Promotion Cost.

Remove column “Promption Cost”, Expand the first column (“Lines” with break), Move it to the place of “Promotion Cost”, create alert with condition “Cell content is not null”, specify =[Promotion Cost USD] in the Display field, configure alignment and color.

Now a puzzle for geeks

You have a table with Lines, Store name, Promption Cost USD, and Sales revenue, the break is defined on the column Promption Cost USD.

Replace expression in the column Promption Cost USD with:

=If [Lines]="Dresses" Or [Lines]="Accessories" Then [Promotion Cost USD] Else 0

and explain the result:

Why is 9,500 shown for “Dresses” and not 13,000?