Show growth value and percentage by one Power BI Card

By in ,
4185
Show growth value and percentage by one Power BI Card

 

In most of the reports, it is a very popular requirement to show the growth value and percentage of metrics over a period of time. In Power BI, the best way is showing these measures by adding Card visuals at the top of the report pages allowing them to be noticed in a glance. In a more informative way, it is good to have both value and percentage in one card providing better insight to users and also takes less space on the report page.

The below image shows the difference in views of having the value and percentage in one card or in two individual cards:

 

As only one measure can be used by Power BI Card visuals, we need to combine the 2 measures into one new measure and  properly format the output text by using the DAX Format function. Below is the structure of Format function where the second parameter is used for setting the format:

   FORMAT(<value>, <format_string>)

For this demo, I have created 2 measures to show sales growth value and percentages of the current year compared to the last year:

  Sales Growth = Sales[Sales Amount]-Sales[Sales Amount LY]

  Sales Growth % = DIVIDE( [Sales Growth],[Sales Amount LY])

 

Let’s create a new measure, to concatenate the two measures above in a way that  Sales Growth gets the  dollar currency format with 2 decimal points(“$#.##” ) and Sales Growth % get percentage format with 2 decimal points(“#.##%”) :

Sales Growth Label = FORMAT ( [Sales Growth] , "$#.##" ) 
                         & " (" & FORMAT ( [Sales Growth %], "#.##%" ) & ")"

 

Using the above measure in a card will result in:

We could combine the 2 measures in one card but as you may have noticed when Sales Growth value is a large number, showing all digits doesn’t look nice. To improve that, we can apply a logic by IF statements to make our format similar to Auto format display unit  in Power BI visuals and show the numeric value in  millions(M), thousands(K) or as full digits based on the measure’s value:

Sales Growth Label = IF (
   ABS ( [Sales Growth] ) / 1000000 > 1,
   FORMAT ( [Sales Growth] / 1000000, "$#.##" ) & "M ",
   IF (
       ABS ( [Sales Growth] ) / 1000 > 1,
       FORMAT ( [Sales Growth] / 1000, "$#.##" ) & "K ",
       FORMAT ( [Sales Growth], "$#.##" ) & " "
   )
) & " (" & FORMAT ( [Sales Growth %], "#.##%" ) & ")"

 

Now the unit is changing based on the value of the measure and you can see different formats based on the value: 

 

 

 

 

Post Video 

 

 

Leave a reply

Your email address will not be published. Required fields are marked *