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”

)

)

)

)

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:

- “KPI”
- Create one measure for each KPI (if you prefer to have full control of the KPIs used/shown in the reports)

- “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.

- “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:

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]

)

With the [KPI_Switch] – measure looking up the different KPIs defined in column “Argument1” like so for example:

KPI_Switch =

SWITCH (

TRUE (),

MAX ( IndividualAccountsLayout[Argument1] ) = “KPI_Cogs%”, [KPI_Cogs%],

MAX ( IndividualAccountsLayout[Argument1] ) = “KPI_Profit%”, [KPI_Profit%]

)

Where KPI_Divide looks like so:

KPI_Divide =

IF (

OR ( [Type_] = “Divide”, [Type_] = “KPI_PreviousRow” ),

DIVIDE ( [MArg1], [MArg2] ),

BLANK ()

)

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).

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] )

)

)

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

Edit 14-Dec-2017: Now that we can right-align text-measures, the SWITCH-measure that SamDuoNing has posted in the comments is the way to go!

Enjoy & stay queryious 🙂

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

That’s nice – thx a lot!!

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

Yes, that correct: VALUES returns a table. But there is sth special about VALUES: If this table consists of only on row, then it can also be used as a scalar. Thus, my COUNTROWS(VALUES….)>1 then Blank otherwise VALUES. (See here for example: https://powerpivotpro.com/2014/08/the-many-faces-of-values/ )

So your VALUES-Table must consist of more than one row.

Pingback: Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports – The BIccountant

Found a way to solve the Cog% format issue, ActKPI Format =

SWITCH (

TRUE (),

[Type1] = BLANK() , FORMAT([ActSign], “Currency”),

[Type1] = “KPI”, FORMAT([KPI Switch], “Percent”),

[Type1] = “DIVIDE”, FORMAT([KPI DIVIDE Percent],”Percent”)

)

Hope this helps.

Hi Sam,

Thanks a lot!

Now that we can right-align text-measures, this is the way to go!

Cheers, Imke

I apologize for my ignorance as I’m still very new to DAX… but in the ActKPI switch measure, it shows:

[Type_] = “KPI”,[KPI_Switch], but Argument 1 for KPI is “KPI_Profit%”. Is that not the measure you would specify in the ActKPI switch statement? Or is there another measure somewhere called [KPI_Switch] that I’m not seeing?

Thanks!

Hi Stacey,

the [KPI_Switch] is another measure that looks up /collects the different meaures of type “KPI” within one measure.

Somehow I forgot to describe this in the article. I’ve included that now. Please also check out the download file with the working solution of this method.

/Imke

You rock Imke!

thanks!

Thanks Mack 🙂

You’re welcome!