Power bi measure by category + Examples

In this Power bi tutorial, we will discuss everything on power bi measure by category with different examples. And also we will discuss the below points:

Power bi measure by category
Power bi measure sum by category
Power bi measure count by category
Power bi measure per category
Power bi measure percentage of category
Power bi measure count per category
Power bi measure sum per category
Power bi measure average per category
Power bi quick measure average per category
Power bi quick measure max per category

Table of Contents

Power bi measure by category

Here we will see how to use the Dax measure as a category in power bi using sample data. And this is a very useful concept when we switching from one measure to another measure using a slicer.

Also, we can visualize the measure such as a column chart where we can assign them to a legend.

Open power bi desktop, Load the data using get data
Then click on the new measure from the ribbon to calculate Total Sales.
Then write the below measure:

TotalSales = 
SUM ( financials[ Sales] )

In a similar way create the measure such as total Gross sales, Total Discounts, Total COGS, and total profit.

TotalGrossSales = 
SUM ( financials[Gross Sales] )

TotalDiscounts = 
SUM ( financials[Discounts] )

TotalCOGS = 
SUM ( financials[COGS] )

TotalProfit = 
SUM ( financials[Profit] )

Now we will create a calculated table, in that we will write the formula to categorize the measure as non-deduction and deduction.

So, click on the Modelling tab -> Then click on the New table from the ribbon.
Then write the below Dax formula:

Financials Category Table = 
DATATABLE (
    "Category", STRING,
    "Type", STRING,
    "Index", INTEGER,
    {
        { "Gross Sales", "Non-Deduction", 1 },
        { "Discounts", "Deduction", 2 },
        { "Net Sales", "Non-Deduction", 3 },
        { "Cost of Goods Sold", "Deduction", 4 },
        { "Profit", "Non-Deduction", 5 }
    }
)

Now we will call the five measures from the financial table. So, for this we will create a measure.
Click on the new measure from the ribbon in Financial category table.
Then write the below measure:

FinancialMeasure = 
VAR SelectedMeasure_ =
    SELECTEDVALUE ( 'Financials Summary Table'[Index] )
RETURN
    IF (
        HASONEVALUE ( 'Financials Summary Table'[Index] ),
        SWITCH (
            SelectedMeasure_,
            1, [Total Gross Sales],
            2, [Total Discounts],
            3, [Total Net Sales],
            4, [Total Cost of Goods Sold],
            [Total Profit]
        )
    )

Now we will create an another measure which will calculate the % of measure over total gross sales.
For this click on New measure from the ribbon. Then write the below measure:

%Finacials = 
DIVIDE ( [Financials], [Total Gross Sales] )

Now to see the result, select the stacked column chart from the visualization pane.
In the Axis field, add the category column from the field pane.
In the legend field, add the type column from the field pane.
In the value field, add the financial measure( measure ) from the field pane
In the tooltip, drag and drop the percentage financial measure from the field pane, when you hover over the bar chart it will show the percentage.

In the below screenshot you can see the measure are categorized by Type i.e. deduction and non-deduction.

Read How to create a measure in Power BI

Power bi measure sum by category

Here we will see how to sum the value by category using the measure in power bi desktop. We will use the below sample table to find sum by value by category using a measure.

For example, For category AA it will show the value as 10+50=60.

Load the data using get data.
Then click on the New Measure from the ribbon in Power bi desktop.
Then write the measure is:

Measure = CALCULATE(SUM(SumCategory[value]),ALLEXCEPT(SumCategory,SumCategory[Category]))

Now we will check the result, so for this select table visual from the visualization pane.
In the value field, drag and drop the category, value and measure from the field pane.

Read Power bi measure subtract

Power bi measure count by category

Here we will see how to count the values by category in power bi.

We will use the below sample table to count the values by category in power bi.

For example FourPillar category, safe=3 and Non safe= 0 and PPE category, Safe=1, Non safe=2.
To get the Result first we will unpivot the FourPillar and PPE column by using Power query.
In power bi desktop ribbon, click on the transform data to open power query.
In power query, click on the transform tab, then select the FourPillar column and PPE column, click on the unpivot column in the ribbon.

After unpivot column it will look like the below screenshot.

Then click on the Attribute column, renamed the attribute column to Category.
Then click on Close and apply.

In power bi desktop, click on the New measure from the ribbon.
Then write the measure:

Safe = var temp= CALCULATE(COUNT(Table[Value]),FILTER('Table','Table'[Value]="Yes")) return IF(ISBLANK(temp),0,temp)

Then write another measure for unsafe:

UnSafe = var temp= CALCULATE(COUNT(Table[Value]),FILTER('Table','Table'[Value]="No" )) return IF(ISBLANK(temp),0,temp)

To check the result, select the table visual from the visualization pane.
In the value field drag and drop the Category column, safe and unsafe measure.

Read Power BI Measure IF with Examples

Power bi measure per category

Here we will see how to calculate the average per category using the measure in Power bi. We will use the below sample table to find the average per category.

For example the average of category A is (1+3)/2=2.
Load the data using get data.
Then click on New measure from the ribbon in power bi destop.
Then write the below measure:

Average = 
CALCULATE (
    AVERAGE ( 'Table (2)'[Value] ),
    FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[Category ] = MAX ( 'Table (2)'[Category ] ) )
)

To check the result, select the table visual from the visualization pane in power bi desktop.
In the value field drag and drop the category column, value column and Average measure from the field pane.

Read Power BI Date Function with Examples

Power bi measure percentage of category

Here we will see how to calculate percentage of value by category in power bi. We will use the below sample data to calculate percentage of value of each category of each month.

Load the data using get data in power bi desktop.
Now we will create a measure to calculate the percentage of value of category of each month.

Measure = (CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]=MAX('Table'[Category])&&'Table'[Month]=MAX('Table'[Month]))))/CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))

To check the result, select the stacked column chart visual from the visualization pane.
In the axis field, drag and drop the category column from the field pane.
In the legend field, drag and drop the month from the field pane.
In the value field drag and drop the measure from the field pane.

Power bi measure count per category

Here we will see how to count value per category using measure in power bi.

We will use the below sample table how much spend by country and advertiser, along with distinct brand count per country per advertiser.

Load the data using get data.
We will create a measure to distinct count the Brand and groupby country, for this click on New measure from the ribbon.
Then write the below measure:

Measure 4 = CALCULATE(DISTINCTCOUNT('Table'[Brand]), GROUPBY('Table','Table'[Country]))

Now we will check result by using the treemap visual, select the treemap visual from the visualization pane.
In the Group field, drag and drop the country column from the fied pane.
In the details field, drag and drop the Advertiser column from the field pane
In the value field, drag and drop the measure4 from the field pane.
In the tooltip field, drag and drop the spend from the field pane.
Now when you hover over the visual it will show the brand count per country.

Read Power bi show items with no data

Power bi measure sum per category

Here we will see how to calculate sum per category by using measure in power bi.

We will use the below sample to calculate the total same per product and then rank the product according to sales.

Now we will calculate The total sales by using SUM function in measure. So click on the New measure from the ribbon.
Then write the below measure:

TotalSales = SUM('Table (3)'[sales])

In similar way add a new measure to calculate the total sales by product.
Then write the below measure:

TotalSales by Product = 
SUMX(
  VALUES('Table (3)'[Product]),
  CALCULATE([TotalSales], ALL('Table (3)'[Date]))
)

Now, create a new measure to calculate to rank the product by total sales by product.
Then write the below measure:

SaleRank = 
  RANKX(
     ALL('Table (3)'[Product],'Table (3)'[Date]), [TotalSales by Product],,DESC,Dense)

Now we will check the result, for this select the table visual from the visualization pane.
In the value field, drag and drop the date column, product column, Total sales measure, sales by product measure and sale rank measure from the field pane.

Read How to create a filter in Power bi

Power bi measure average per category

Here we will see how to calculate power bi measure average per category. We will use the below sample table to calculate the average of group score based on each group.

To calculate the average of group score create a new measure, by clicking on the New measure from the ribbon. Then write below measure:

Average1 = 
AVERAGEX(
    DISTINCT(SELECTCOLUMNS(Table4, "Group", Table4[Group], "GroupScore", Table4[GroupScore])), 
    [GroupScore]
)

Now to check the result, select the card visual from the visualization pane.
In the field, add the Average1 measure from the field pane.

Power bi quick measure average per category

Here we will see how to calculate average value per category using quick measure in power bi desktop.

We will use the below sample table to calculate average sales per product using the quick measure.

power bi quick measure average per category

First we will calculate the Total sales, click on theNew measure from the ribbon.
Then write the below measure:

TotalSales = SUM('Table3'[sales])

Now we will find the average of total sales per product, so, for this click on the quick measure from the ribbon.

Then quick measure window will open, under calculation select average per category.
Expand the table under field section, drag and drop total sales(measure) under the base value and product column under the category.
Then click on OK.

Once you click on OK, you can see the measure.
Now, we will check the measure, so, for this select the table visual from the visualization pane.
In the value field, drag and drop the Product column, total sales measure, and Total sales average per product(quick measure).

Read Power bi show value as percentage

Power bi quick measure max per category

Here we will see how to calculate max value per category using quick measure in power bi.

We will use the below sample table to calculate the max total sales per category using the quick measure.

First we will calculate the total sales, for this click on the New measure.
Then write the below measure.


Total Sales = SUM('Table3'[sales])

Now we will calculate the max of total sales per category, so click on Quick measure from the ribbon.

Now Quick measure window will open, under calculation select the max per category from the list.
Expand the table in the field, Under base value drag and drop total sales measure.
Then under category, drag and drop the product field from the field section. Then click on Ok.

Now you can see the measure, in the header part of power bi.
To check the measure click on the table visual from the visualization pane.
Then in the value field, drag and drop the product, total sales measure, and total sales max per product( quick measure).

Power bi quick measure weighted average per category

Here we will see how to calculate the weighted average per category using a quick measure.

We will use the below sample table to calculate the weighted average per category using a quick measure.

power bi quick measure weighted average per category

To calculate the weighted average per category, click on the Quick measure from the ribbon in power bi desktop.
Quick measure window will open, under calculation click on the weighted average per category.
Expand the table under the field section, then undr the base value add the Cost column.
Under the Weight, add the count column from the field pane.
Under the category field, add the product column from the field pane. Click on Ok.

Once you click on Ok, you can see the measure header part of the power bi desktop. Now check the Quick measure by adding measure to table visual.
Select the table visual from the visualization pane.
In the value field drag and drop the productcolumn, cost column, count column, and Cost weighted by count per product (quick measure) from the field pane.

You may like the following Power Bi tutorials:

In this Power bi tutorial, we learned about power bi measure by category with example. And the below points:

Power bi measure by category
Power bi measure sum by category
Power bi measure count by category
Power bi measure per category
Power bi measure percentage of category
Power bi measure count per category
Power bi measure sum per category
Power bi measure average per category
Power bi quick measure average per category
Power bi quick measure max per category

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com