cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Get Total of instances per month and Rank them

Hi All,

I am new to PowerBI so please excuse me several of my terms are hard to understand.

So what I am trying to do is get the monthly count of a specific issue from a table.  For example, Issue1 appeared 24 times in January and then 3 times in Feb, while Issue 2 appeared 3 times in Jan and then 4 times in Feb.  Then I would like the data to be displayed this way on a table:

January         February

Issue1           24                 3

Issue2           3                   4

I do have a table that has the Date and the Issue name and was wondering what the procedure would be to show the data in that way.

A follow up concern that I have is ranking the results for the above mentioned table.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Get Total of instances per month and Rank them

Hi @Idlehands,

For your requirement, please create calculated the column to recognize month, and calculate the count of name in each month. Then create a matrix shows what you want. I try to reproduce your scenario and get expected result.

First, I create the following sample date.

Then create several calculated column using the formulas below.

```Month = MONTH(Table1[Date])

Times = CALCULATE(COUNTA(Table1[Name]),ALLEXCEPT(Table1,Table1[Month],Table1[Name]))

Rank1 = RANKX(FILTER(Table1,Table1[Month]=EARLIER(Table1[Month])),Table1[Times],,ASC,Dense)```

Finally, create a matrix, select the Name as Row level, the Month as columns level, the calculated columns as Values level, please see the following screenshots. The there are 3 issue1 times, 3 issue2 times in Jan, and rank them. While there are 3 issue1 times, 2 issue2 times in Feb, and rank them.

Best Regards,
Angelia

4 REPLIES 4
Highlighted
Microsoft

## Re: Get Total of instances per month and Rank them

Hi @Idlehands,

For your requirement, please create calculated the column to recognize month, and calculate the count of name in each month. Then create a matrix shows what you want. I try to reproduce your scenario and get expected result.

First, I create the following sample date.

Then create several calculated column using the formulas below.

```Month = MONTH(Table1[Date])

Times = CALCULATE(COUNTA(Table1[Name]),ALLEXCEPT(Table1,Table1[Month],Table1[Name]))

Rank1 = RANKX(FILTER(Table1,Table1[Month]=EARLIER(Table1[Month])),Table1[Times],,ASC,Dense)```

Finally, create a matrix, select the Name as Row level, the Month as columns level, the calculated columns as Values level, please see the following screenshots. The there are 3 issue1 times, 3 issue2 times in Jan, and rank them. While there are 3 issue1 times, 2 issue2 times in Feb, and rank them.

Best Regards,
Angelia

Highlighted
Regular Visitor

## ksRe: Get Total of instances per month and Rank them

Thanks!

I will try this out later and will let you know if it worked.

Thanks!

Highlighted
Microsoft

Hi @Idlehands,

Best Regards,
Angelia

Highlighted
Regular Visitor

## Re: ksRe: Get Total of instances per month and Rank them

That did the trick!

Thanks a lot!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors