In MS PowerPivot for Excel you can add your own calculations to your pivot table. A useful trick is to add the formatting you want straight into the formula. If you use a division to calculate a percentage, it helps the user/analyst to see the percentage on the screen instead of 0.046.
With the DAX function FORMAT() you can apply formatting straight away. The downside of this formula is that the output is text, so you cannot calculate with it anymore. That does not have to be a problem if the answer is only used for analysis. Another big downside is that you cannot apply conditional formatting anymore, because the result has become text. This is my biggest objection.
Example division, output = percentage %:
=FORMAT(IF(SUM(FactWerknemerKPI[UrenRooster])=0,0, SUM(FactWerknemerKPI[AantalUrenGewerktProductief]) / SUM(FactWerknemerKPI[UrenRooster]) ),“Percent”)
What I do here is calculate the productivity. First I check that the denominator of the division is not 0 (there are no NULLs in the DB). If it is, the result is always 0. Otherwise I divide the number of hours worked by the scheduled hours. Finally I FORMAT the result to PERCENT.
Frequently asked questions
How do I show a percentage instead of 0.046 in PowerPivot?
Use the DAX function FORMAT() with the option "Percent" to show the result of your division as a percentage straight away. The analyst then sees 4.6% on the screen instead of 0.046.
What does the DAX function FORMAT() do in PowerPivot?
FORMAT() applies formatting to a calculation in your pivot table. The result is text, though, so you cannot calculate with it anymore.
Why doesn't conditional formatting work after FORMAT() in PowerPivot?
FORMAT() returns text and not a number. You cannot apply conditional formatting to text anymore. That is the biggest objection to this function.
How do I prevent a division by zero in a DAX formula in PowerPivot?
First use IF to check whether the denominator is 0. If it is, return 0. Otherwise divide the hours worked by the scheduled hours and apply FORMAT.
When is FORMAT() in PowerPivot not a problem?
When you only use the result for analysis. Then it does not matter that the result is text and that you cannot calculate with it.
2 comments
Waarom gebruik je niet de formating tool (eigenschappen) in het groene datascherm van PowerPivot. Daar kun je aangeven of je een percentage wil hebben met 0, 1 of 2 decimalen.
Omdat het een berekend veld is in de draaitabel. Dit berekende veld zit niet in het groene scherm :-)
Related blogs
SQL output in Excel shows millions
Tablix filter with the LIKE operator
SSRS Sparkline: NOW and TREND