Excel, PowerBI Flashcards
DAX
Data analysis expression is a language used to create formulas in PowerBI while M language is used for scripting in Power Query
Data modeling
Creating connections between tables, primarily involving two kinds of tables: Dimension (Primary) tables and Fact tables
OLTP
Online Transaction Processing which are critical for organization like. Sales software DB, CRM DB
OLAP
Online analytical processing - DWH
Gross Margin
Gross margin is a financial metric that measures the percentage of revenue remaining after deducting the cost of goods sold (COGS). It reflects how efficiently a company produces and sells its products, leaving funds for other expenses such as operating costs, taxes, and profits.
Formula for Gross Margin:
\text{Gross Margin} = \frac{\text{Revenue} - \text{COGS}}{\text{Revenue}} \times 100
Key Components:
1. Revenue: Total income generated from selling goods or services.
2. COGS: Direct costs of producing goods or services (e.g., raw materials, labor).
Example:
If a company generates $100,000 in revenue and its COGS is $60,000:
\text{Gross Margin} = \frac{100,000 - 60,000}{100,000} \times 100 = 40\%
Interpretation:
• A higher gross margin indicates better efficiency in managing production costs, leaving more for profits and other expenses.
• A lower gross margin may signal higher production costs or inefficiencies.
Net Profit
Measures in PowerBI
Calculated Columns in PowerBI
Calculate function in DAX
Mean
Synonym to average
Median
After arranging the numbers in ascending or descending order, in the case of odd numbers, the middle number is the median and in case of even numbers, the average of the two middle numbers is the the median
Mode
Most frequently occurring value in data set
Variance
Variance quantifies the spread of a distribution by calculating the average of squared differences from the mean. It’s a crucial measure used to assess the dispersion of a dataset.
The formula for variance is:
For a dataset with n data points (x1, x2, x3, …, xn):
Population Variance (σ²):
σ² = (∑i=1ⁿ (xi - μ)²) / n
Where:
- σ²: Population variance
- xi: Each data point
- μ: Population mean (μ = ∑xi / n)
- n: Number of data points in the population
Standard deviation
square root of variance. It is the amount of variation or disperson of a set of values.
Smaller the standard deviation, the closer the data points are to the mean. Conversely the larger the standard deviation, ther more spread out the data points are
Correlation
A statistical measure that shows the degree to which two variables are related
Correlation coefficient can range from -1 to 1
-1 (perfect negative correlation) < 0 (no correlation) < 1 (perfect positive correlation)
power query language
M language used in power query
Inner Join
An inner join returns only the rows where there is a match between both tables. If a row in one table doesn’t have a matching row in the other table, that row is excluded from the result.
Example:
Table 1 (Customers): CustomerID, CustomerName Table 2 (Orders): OrderID, CustomerID An inner join on CustomerID will return only those customers who have placed at least one order.
Result: Only customers with corresponding orders will appear in the final result.
Left outer join
An inner join returns only the rows where there is a match between both tables. If a row in one table doesn’t have a matching row in the other table, that row is excluded from the result.
Example:
Table 1 (Customers): CustomerID, CustomerName Table 2 (Orders): OrderID, CustomerID An inner join on CustomerID will return only those customers who have placed at least one order.
Result: Only customers with corresponding orders will appear in the final result.
Right outer join
A right outer join works similarly, but it returns all rows from the right table and the matching rows from the left table. Rows from the right table without matches in the left table will still appear, with null values for the left table’s columns.
Example:
You would get all orders (right table), even if there are no corresponding customers in the left table.
Full outer join
A full outer join returns all rows when there is a match in either table. Non-matching rows from both tables will still be included in the result, but with null values where the tables don’t match.
Example:
You’ll get all customers (left table) and all orders (right table), even if some customers haven’t placed orders or some orders don’t have customers associated.
Conditional column in power query
A Conditional column allows you to add a column to your
table based on a specific condition or set of conditions.
Custom column in power query
A Custom column allows you to add a new column to
your table using a formula that you define. This formula
can reference other columns in your table and perform
calculations or manipulations on their values
