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:
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]
)
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).
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] )
)
)
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!
Hi Imke
I’m trying to set this up in excel 2013 and I am getting errors when adding in the MArg1 and MArg2 measures. The error states “The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.”
If I change this to MAXA instead I get a different error ” Comparison operations cannot be performed on values of incompatible types. Ensure that both values are Numeric, String, or Boolean(True/False). Consider using the VALUE or FORMAT function to convert one of the values.”
My knowledge on DAX functions is limited but I love this concept of not having to set up a KPI for every instance. Is there an alternative function that can be used to achieve this step..??
Hi Kelly,
not sure what’s going wrong in your case – this should work in Excel as well.
Make sure that your columns are formatted correctly to numbers in Power Query already.
Also make sure that you don’t format anything to percentage, using DAX FORMAT(…, “Percentage”), as this will convert numbers to text!!
/Imke
Thanks for the Reply Imke
Are you saying I need to format the columns of “Description in Report” and “Argument 1” as a number even though they are text??
I had a look in the resource attached to the blog, and the columns are formatted as text and the “ActSign” measure is a value. This is the way I’ve formatted my columns coming through via Power Query, I also checked the data model and the formats are same per set during Power Query.
The Max function in excel seems to only work with values not text so I tried the MAXA which does allow text. That was the second error I received.
is there a way to separate this into two steps it’s not preferred but I don’t know how else to get this to work as it working with Text columns. It’s the last hitch in the reports I’m developing, I’d really love to get it working.
MArg1 =
CALCULATE (
[ActSign],
FILTER (
ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report]
= MAX ( IndividualAccountsLayout[Argument1] )
)
)
Hi Imke,
I’m so happy, I’ve just managed to get it to work using a combination of LASTNONBLANK and VALUES. It must made my day!!
=CALCULATE([A_AUD],FILTER(ALL(AccountsGroupsDetails),AccountsGroupsDetails[Normalised Account Name]=LASTNONBLANK(Values(AccountsGroupsDetails[Argument 1]),0)))
Hi Kelly,
great, that you made it and thanks for posting your solution !!
/Imke