Quick Calc and Format of column are two much requested features. In this article I’ll explain how to use this two Features in Power bi.
Conditional Formatting allows users to color cell backgrounds based on the value of the data in the cell. The coloring follows a gradient based on the data value. Today it’s the only conditional formatting feature. It’s not possibile use for example the other Conditional formatting feature of Excel.
We can use Conditional formatting both on measure or numeric column, it’s work only on table and matrix. To access conditional formatting settings right click or click the arrow of the measure you want to format:
Then you can configure the color and the min/max values associated. If you select Diverging it’s possible insert a third colour. It’s also possible insert a fixed value for lowest and highest value.
In the new version it’s possible to define how to consider null value, we have 3 different possibility:
- Insert 0
- Insert a specific value
- Not to consider in the conditional Formatting
The result is a formatted table that overrides any custom table styles for the conditionally formatted cells.
To remove conditional formatting, right click the measure again:
More details about Conditional formatting in the following video:
Quick Calc is an interesting feature on power bi desktop. Its works on all columns of your dataset in all charts or tables. The only important things to do if you want to use quick calc is insert the specific column in the Value tab of your table or chart:
Quick calc woks in two different ways based on the column type that you have used. To access Quick calc settings right click or click the arrow of the column you want to calc and select calcolo rapido or Quick calc.
If you select a text column you can:
- Show only the first attribute
- show only the last attribute
- Count the attribute
- Distinct count the attribute
If you select a numeric column you can:
- Sum of value
- find the minimum or maximum value
- Average the value of column
- standard deviation of value
- Count the value
- Distinct count of value
- Variance fo value
- Median of value
To select one of these in the Quick calc windows you have to select the first drop down menu and select your Calc.
After you have selected your calc you can define how to show the specific value with the second drop down menu:
- As its calculated value
- As % of the total
- as % of a column
- as % of a row
when you finish choosing which calculation to use and how to show it you have only to click OK and now you have your Calculation based on a column. The “show format” works not only with column but also with Measure.