Latest Enterprise DNA Initiatives

Switching signs to create P&L visual

Having viewed the Financial Reporting workshop, I’m trying to similarly create signed values for Expenses, in order to create a true P&L view (image #1). In this case, I want to make Expenses be negative, in order to have a net margin result. However, while the visual shows the right Total, the sub-totals are wrong in that they display the Total but not the true sub-totals (image #2):

Source data for sign issue

The following DAX formula is working for me. Notice how you can easily apply the sign change for any financial statement category (Expenses, Cost of Products, Revenue, etc.) that requires a particular sign:

// Switch sign (based on a criterion)
Actual (sign) = sumx(‘Combined dataset’, ‘Combined dataset’[Values] * if(‘Combined dataset’[DataSet] In {“Cost of Products”},-1,1))