KPIs in Easy Profit and Loss for PowerBI

Welcome to the last part of my Easy Profit & Loss series where I will cover KPIs in rows & columns:

1) KPIs in columns

Show all your figures as a percent of turnover for example: Nice & easy: Divide current figure by the total sum of turnover:

Turnover% =
ABS (
    DIVIDE (
[ActSign],
        CALCULATE (
[ActSign],
            FILTER (
                ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report] = “Income”
            )
        )
    )
)
DAX Formatter by SQLBI

We need to leave the current row context to retrieve the turnover-value in each row, therefore the ALL.

2) KPIs in rows

The KPIs (that shall be shown in rows, within the same column of the main values) come in 3 different flavours:

  1. “KPI”
    • Create one measure for each KPI (if you prefer to have full control of the KPIs used/shown in the reports)
  2. “Divide”
    • Let the report designer create its own KPIs by editing the nominator (“Argument1”)  and denominator (“Argument2”) column in the report design table. They reference to existing rows in the report-definition. So you don’t have to define any KPI-measures in advance.
  3. “KPI_PreviousRow”
    • This sweet little cracker is my favourite, as it combines the advantages of the two approaches above: You don’t have to define all your possible KPI measures in advance, but just those for the denominator. The value for the nominator will automatically be given as the previous row. So the report designer has the flexibility to create his own KPIs but is prevented to make stupid errors.

To make this magic happen, there are 3 new columns in the report design table:

3 new column in the report design

For the standard KPI (1) you just fill in “KPI” in the Type column and the name of the measure in the column “Argument1”.

The Divide-measure (2) gets a “Divide” in the “Type” column, in the “Argument1” the nominator and the denominator goes into “Argument2”.

For the KPI_PreviousRow (3) the first argument gets the denominator, as the nominator is no argument (but by default the value of the previous row).

All that’s left now is to create the SWITCH-measure that chooses the measure accoring the entry in the “Type” column:

ActKPI =
SWITCH (
    TRUE (),
[Type_] = BLANK (), [ActSign],
[Type_] = “KPI”, [KPI_Switch],
[KPI_Divide]
)
DAX Formatter by SQLBI

Where KPI_Divide looks like so:

KPI_Divide =
IF (
    OR ( [Type_] = “Divide”, [Type_] = “KPI_PreviousRow” ),
    DIVIDE ( [MArg1], [MArg2] ),
    BLANK ()
)
DAX Formatter by SQLBI

We don’t need a separate measure for the KPI_PreviousRow, because it is included in the KPI_Divide. Therefore I’ve transformed all these rows into the form of the KPI_Divide: Moved the denominator that stood in “Argument1” to “Argument2” (red) and evaluated the previous row that then went into “Argument1” as the nominator (green).

Definition in report design

Result after transformations: Harmonized with Type “Divide”

As you can see – the possibilities to avoid DAX are manifold 😉 . Of course, you can reduce the options or add others, this is just to give you an idea of a possible setup.

Measure MArg1 contains the value for the first argument:

MArg1 =
CALCULATE (
[ActSign],
    FILTER (
        ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report]
MAX ( IndividualAccountsLayout[Argument1] )
    )
)
DAX Formatter by SQLBI

Again, a simple FILTER/ALL-construct, but now working as a variable lookup instead of referencing a fixed value: It fetches the value where the “Description in Report” has the same value than the entry in column “Argument1” of the current row. (The MAX here is only needed to transform the format of what “IndividualAccountsLayout[Argument1]” returns (a column) into a scalar value (because only scalars can sit on the right side of an equation/comparison). So a MIN would work just as good.)

When we drag that new measure to the report pane, we have to deal with an unpleasant compromise, that hopefully will be solved soon: We cannot use different number formats within one column in PowerBI. So we cannot show them as percentage while leaving the main values as (decimal) numbers. Although DAX provides format functions allowing us to apply specific formats to numbers, those results will actually become text-values and will be aligned to the left. No chance currently to adjust the alignment in PowerBI. So please vote for this feature here: AlignColumnContent .

But to compensate for this disappointment, you get some squared traffic lights (sorry, no smart DAX-formatter for UNICHAR availabe yet):

TrafficLight = UNICHAR(IF([Diff%]<>BLANK(),IF([Diff%]>0,1,128215,0,IF([Diff%]>-0,05,128210,0,128213)),BLANK()))

A measure that returns different UNICHAR characters, depending of the differences between actual and budget figures ([Diff%]). Here the thresholds are set to green if more than 10% over budget, red if more than 5% below budget and yellow for everything in between. Thanks to Chris Webb for bringing this cool feature to my attention. (If you know the numbers for green/yellow and red circles, of course please post a comment or send me an email!)

Another feature that’s worth mentioning: You can hide the accounts details by selecting “No” in “IsAccounts”:

A quasi-drillthrough for tables in PowerBI (another feature that’s currently missing). (In this report, it doesn’t look so good, because of its asymetric design. But you can design reports where this feature produces good looking reports in both modes).

Download for logged-in subscribers AcctScheme4_KPIV5.zip

Enjoy & stay queryious 🙂

Comments (4) Write a comment

  1. Some color symbols, not the right ones but funny

    Green Heart: 128154
    Yellow Heart: 128155
    Red Heart 128151

    Blue Circle: 128309
    Red Circle: 128308

    Yellow Diamond: 128310
    Blue Diamond: 128311

    Red Triangle up: 128314
    Red Triangle down: 128315

    Reply

  2. Thank you for the great post. It’s something that I have been looking for. Just started my Power BI journal, I have a question about
    Type_ = IF(COUNTROWS(VALUES(IndividualAccountsLayout[Type]))>1.0,BLANK(),VALUES(IndividualAccountsLayout[Type]))
    FALSE will return VALUES(IndividualAccountsLayout[Type]), and I think VALUES() is affected by the filter in the table, however, isn’t the VALUES() a table itself?
    I created a new measure Type_Test=VALUES(IndividualAccountsLayout[Type]) and try to put that as one of the columns, The error mesage read “a table of multiple values supplied while a single value was expected.
    Why the VALUES() table in IF() took the filter, while the Type_Test VALUES() didn’t seem to?

    Thank you again for your great post
    Sam

    Reply

Leave a Reply