Skip to main content

20 Power BI Dax Measures


UserPower bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples:

  1. Total Sales:

    scss
    Total Sales = SUM(Sales[Amount])

  2. Average Sales Price:

    scss
    Avg Sales Price = AVERAGE(Sales[Amount])

  3. Total Units Sold:

    mathematica
    Total Units Sold = SUM(Sales[Quantity])

  4. Total Customers:

    scss
    Total Customers = COUNTROWS(Customer)

  5. Total Products:

    mathematica
    Total Products = COUNTROWS(Product)

  6. Maximum Sales Amount:

    scss
    Max Sales Amount = MAX(Sales[Amount])

  7. Minimum Sales Amount:

    scss
    Min Sales Amount = MIN(Sales[Amount])

  8. Sales Growth Percentage:

    mathematica
    Sales Growth % = (Total Sales - [Total Sales Last Year]) / [Total Sales Last Year]

  9. Total Profit:

    scss
    Total Profit = SUM(Sales[Profit])

  10. Total Orders:

    scss
    Total Orders = COUNTROWS(Orders)

  11. Total Customers with Sales:

    css
    Total Customers with Sales = COUNTROWS(FILTER(Customer, [Total Sales] > 0))

  12. Total Sales by Region:

    mathematica
    Total Sales by Region = SUMX(VALUES(Region[RegionName]), [Total Sales])

  13. Total Sales by Year:

    scss
    Total Sales by Year = SUMX(VALUES(Calendar[Year]), [Total Sales])

  14. Running Total Sales:

    scss
    Running Total Sales = SUMX(FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])), [Total Sales])

  15. Top N Products by Sales:

    mathematica
    Top N Products by Sales = TOPN(5, ALL(Product), [Total Sales])

  16. Rolling Average Sales (3 Months):

    scss
    Rolling Avg Sales 3M = AVERAGEX(DATESYTD(Calendar[Date]), [Total Sales])

  17. Total Sales to Date:

    mathematica
    Total Sales to Date = TOTALYTD([Total Sales], Calendar[Date])

  18. Sales Rank by Product:

    mathematica
    Sales Rank by Product = RANKX(ALL(Product), [Total Sales], , DESC, Dense)

  19. Customer Churn Rate:

    css
    Customer Churn Rate = DIVIDE([Lost Customers], [Total Customers])

  20. Year-to-Date Growth:

    mathematica
    YTD Growth = DIVIDE([Total Sales], [Total Sales Last Year]) - 1

These measures cover a range of common calculations in Power BI, including basic aggregations, comparisons, and time-based calculations. You can adapt and customize them to suit your specific data and reporting needs

Labels:

Comments

Post a Comment

Popular posts from this blog

Data Analysis Tool Pack

  The  Analysis ToolPak  is an  Excel add-in  program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click on OK. 4. On the Data tab, in the Analysis group, you can now click on  Data Analysis . The following dialog box below appears. 5. For example, select Histogram and click OK to create a Histogram in Excel. Example Rank and Percentile The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list. The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table. The percentile is a percentage that indicates the proportion of the list which is below a given number. Highlight the list (or the cells) which...
Post a Comment
Read more

Sum a cell through several Worksheets

You have 12 identical worksheets, one for each month. You would like to summarize each worksheet. Is there a better way than using =Jan ! B4 + Feb! B4+Mar! B4+Apr! B4…? Solution:  You can use a 3-D formula such as  =SUM(Jan:Dec!B4) , as shown in  Figure 41 . Figure 41. A 3-D formula adds up all instances of B4 on the 12 sheets from Jan through Dec. Late-breaking Tip : To add up cell B4 on all the worksheets with Sales in the sheet name, type  =SUM(‘*Sales’!B4)  and press Enter. If the first or last worksheet contains a space in the name, you have to use apostrophes around the pair of worksheet names:  =SUM(‘Jan 2009: Dec 2009’!B4) . You can easily copy this formula to other cells on the summary worksheet.  Gotcha:  Do not drag the summary worksheet to appear after the Jan worksheet, or you will set up a circular reference. Additional Details:  It is possible to set up a named range that refers to a 3-D range. Here is an interesting way to set...
Post a Comment
Read more

CAGR Dax Measure

CAGR stands for  C ompound  A nnual  G rowth  R ate.  It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis.   The CAGR metric is calculated using the following formula: If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out. We will need to create several measures to calculate the individual pieces the CAGR formula.  Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it.  So, let’s break it down, we will need the following measures to calculate CAGR: A measure to retrieve the First Year in the data set A measure to retrieve the Last Year in the data set A measure to calculate the Number of Years between the First and Last Years in the data set A measure to aggregate the total sales in the data set...
Post a Comment
Read more