Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jaesoonee
Regular Visitor

Group values

Hi everyone,

 

New to Power BI. Simple task but cant seem to figure out which function/s needs to be applied.  Pretty sure I need to create a calculated measure to achieve this.

 

I have a data set with the following normalized column data;

years. 1985, 1990, 1992, 1991, 2000, 2000, 2001 etc.business

business name; various business names

employee; employee names for associated business

 

Question to answer: create a tab/graph which shows per year for every business with less than 10 staff

 

I can figure out the number of companies with less than 10 but dont know how to filter/summarize based on year.

 

Output should look like this.

Year    Count

2000      5

2001      20

2002      14

2003      32

etc

 

Thanks

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@jaesoonee

 

In this scenario, you need to create a calculated table to filter the Business with more that 10 Employees.

 

Table =
FILTER (
    SUMMARIZE (
        Business,
        Business[Year],
        Business[Business],
        "Count Of Employees", COUNTA ( Business[Employee] )
    ),
    [Count Of Employees] > 10
)

Then create a measure in new calculated table to calculate the count of business.

 

 

Count Of Business = COUNTA('Table'[Business])

Drag Year and above measure into a table visual will get your expected result.

 

 

See my sample below:

 

5.PNG

 

66.PNG

 

7.PNG

 

Regards,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@jaesoonee

 

In this scenario, you need to create a calculated table to filter the Business with more that 10 Employees.

 

Table =
FILTER (
    SUMMARIZE (
        Business,
        Business[Year],
        Business[Business],
        "Count Of Employees", COUNTA ( Business[Employee] )
    ),
    [Count Of Employees] > 10
)

Then create a measure in new calculated table to calculate the count of business.

 

 

Count Of Business = COUNTA('Table'[Business])

Drag Year and above measure into a table visual will get your expected result.

 

 

See my sample below:

 

5.PNG

 

66.PNG

 

7.PNG

 

Regards,

dilumd
Solution Supplier
Solution Supplier

I don't think i fully understood your issue. However if you want to create a new table with Year, business name and employee. you can use Summarize function and create a new table.

 

https://msdn.microsoft.com/en-us/library/gg492171.aspx

 

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.