Invert sign
Last updated
Last updated
In the case of financial reports such as the P&L statement, data for expense or deduction items are often shown as positive numbers. This is especially true when the income and expense values come from different tables. However, the report might need to be updated with the negative sign operator for two main purposes.
(a) for performing aggregations, say for naturally aggregating revenue & expense accounts to calculate profit, or
(b) to display +/- signs in the table to indicate which accounts are added vs. deducted.
This can be easily achieved using the 'Invert' option in Inforiver.
You can invert cells or rows and display the negative signs at a cell level. You can also define whether the negative signs should influence the total or not.
Let’s invert the 'Beverages' row in the 'East' subregion.
a) Select the required row. In the ‘Insert’ tab of the toolbar, click the ‘Invert’ option.
b) The row 'East -> Beverages' is inverted and you can see the change in the value of totals.
c) If you do not want the sign inversion to impact the totals, turn off the 'Include in Total' toggle in the Display -> Numbers settings.
d) Click ‘Invert’ again with the respective row selected to reset to the default value.
e) A pop-up opens with a message as shown, select ‘Apply’ to reset.
f) You can now see the original values in the Beverages row and the totals.
g) Instead of inverting the sign of the child nodes individually, you can do it in bulk at the parent level. Let's invert the 'United States' parent row, select the row and apply 'Invert'.
h) You can see that parent and the corresponding child rows are inverted as shown.
i) Note that the signage of calculated rows cannot be changed. In the below image, you can see the 'Invert' option is disabled for the calculated row 'Sum'.
Sign inversion can be shown at the row header level with the ‘Sign in headers’ option.
a) In the ‘Home’ tab of the toolbar, click ‘Display’ -> 'Numbers'.
b) Turn on ‘Sign in headers’ to display the signage in the row headers as shown.
c) You can also show positive signs and equals on other data source rows and total rows by enabling the 'Show positive sign' and 'Show equals on totals' options respectively.
a) Create a new signed measure
This feature is a single-click solution to creating signed measures. To invert a particular measure, select it and click on the 'Invert' button in the Insert ribbon.
Click on Create to insert the new calculated measure in the report.
b) Invert sign for individual measures
You can change the sign for a single measure at both child and parent levels. For a given row, select any cell and click on the ‘Invert’ button. Notice how the sign for the Discount measure has been flipped in the example below.
When hierarchical data is involved, you can also change the sign for a particular measure at the parent level, this will be cascaded to all the child rows.
Instead of defining positive and negative signs at the report level, you can use the sign conventions from your data source tables. To do this, create a column that assigns values -1, +1, and 0 for negative, positive, and totals respectively. A sample table is shown below.
a) Add this sign column in the 'Sign' field. You can see the change in the values in the below image and also the calculation of subtotals and totals.
b) On enabling the 'Sign in header' option, you can see the positive, negative, and total rows with the signs in the header.
c) The 'Invert' option is disabled when the sign column is added.
When a sign field is added, you cannot add column fields.
In the next section, we'll be covering the Audit log feature.
Invert signs for reporting in Microsoft Power BI with Scenarios
