CODEWITHSUNDEEP
powerbidax
Ela
Ela

Reputation: 1

Power BI counting the occurances in a row

Here is my data base:

Name| 1st    | 2nd  | 3rd  | 4th  | 5th

Ann |   five | five | four | five | one

Tom |   four | one  | four | five | four

and what I want to do is to create columns that would contain the number of occurrences in a row, so in this case what I want to achieve:

Name|   1st  | 2nd  | 3rd  | 4th  | 5th  | Five| Four | One    

Ann |   five | five | four | five | one  |  3  |  1   | 1

Tom |   four | one  | four | five | four |  1  |  3   | 1

Upvotes: 0

Views: 1599

Answers (2)

fernandojapp
fernandojapp

Reputation: 1

I have the similar issue here, but my dataframe is not fixed in terms of columns, I mean, for next refresh of database the number of columns may change get bigger (more columns) or smallest (less columns), perhaps the name of column header changes too... In this case, I can't pass the columns name or index for the counter look for, the formula needs look into the entire row no matter how many columns or name of it..

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

Ideally, you want to unpivot your data so that it looks like this:

Name | Number | Value
-----|--------|------
Ann  | 1st    | five
Ann  | 2nd    | five
Ann  | 3rd    | four
Ann  | 4th    | five
Ann  | 5th    | one
Tom  | 1st    | four
Tom  | 2nd    | one
Tom  | 3rd    | four
Tom  | 4th    | five
Tom  | 5th    | four

Then you could easily create a matrix visual like this by putting Name on the rows, Value on the columns, and the count of Number in the values field.

enter image description here

I don't recommend it, but if you need to keep it in your current layout, then your calculated columns could be written like:

Five = (TableName[1st] = "five") + (TableName[2nd] = "five") + (TableName[3rd] = "five") +
       (TableName[4th] = "five") + (TableName[5th] = "five")

The Four and One column formulas would be analogous.

Upvotes: 1

Related Questions