Calculated Measures for Power BI
If you are looking for a Microsoft Power BI Corporate Training in Gurgaon , please feel free to reach out at firstname.lastname@example.org or whatsapp at +91-9953805788
In my previous blog I have explained about adding Calculated column in Power BI.
Today I will explain about adding Measures for Power BI. First of all we will go through differences between Calculated column and Calculated Measure.
Calculated column – Calculated Column are calculated before aggregation at row level.
They are used for row by row calculation, like excel.
Calculated columns are not recalculated when there is any change in filter on dashboard.
Calculated columns stored inside our data set. Calculated column takes up space in RAM memory. So it should not be used when we have very large data e.g. in million.
Calculated columns act like a normal column and can be used in relationship and calculations.
Calculated columns are used when need to slice or filter on value
Calculated Measures: Measures are calculated after aggression. They are used for calculations across rows
Measure are calculated every time when we recreate/ recalculate visualization or use filters, it is dynamic thing and calculate on fly when required. It Returns different answer depending on the filters applied
Measures are Not stored in RAM, It consumes CPU.
Measures can be used on other measures.
Measures can be moved between tables.
Measures cannot be used for relationships.
Measures are used to calculate percentages, calculate rations, and need complex aggregations
Load Sample file in Power BI:
Right Click on Profit field:
Here we can see New Measure in menu, click on it:
Click on New measure:
It will add new column as measure we can give some different name to this field and add formula to calculate Profit Margin (Sum of profit field divided by the sum of Sales field)
Now we will select sales and Profit Margin for map:
Go to format
Change colours for Minimum and maximum:
Drag State and city to in country hierarchy:
Select any record from map:
Value for Profit margin is in whole number, let we change it in %
Select Modelling menu and select % sign for Profit Margin field:
Select any record:
we can see Percentage value for Profit Margin.
Drill map by using:
Here we checked that Calculated Measures are changing with filter.