Forum Discussion

gthistlethwaite's avatar
gthistlethwaite
Frequent Visitor
8 years ago
Solved

CountRows of Multiple Columns

I have 4 columns of data. I need to sum the 4 columns based on a text value.   I was able to perform on 1 column, but then receive an error trying to add these together.   AsiaTTL = COUNTROWS(FI...
  • gthistlethwaite's avatar
    gthistlethwaite
    8 years ago

    Fixed now, it required an extra closing bracket before the next +COUNTROWS:

     

    AsiaTTL = COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat1]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat2]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat3]="Asi"))+COUNTROWS(FILTER('QHSE Award Voting','QHSE Award Voting'[Cat4]="Asi"))

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Looks like you have 4 columns that are all award based, but are different categories.

 

I would UNPIVOT the data in Query Editor.  Highlight all 4 columns, then right click one of the column headers.  Click "Unpivot Columns".

 

You'll end up with 2 columns:  One will be the 4 different Categories (Cat1, Cat2, Cat3, Cat4), and the other will be the country ("Asia", etc).

 

Now your DAX measure will be this:

 

[# Awards] = 
CALCULATE(
    COUNTROWS('QHSE Award Voting'),
    Table[Value] = "Asia"
)

If you rename the Attribute and Value columns that Query Editor automatically gives (which I recommend), be sure and update the formula above.

gthistlethwaite's avatar
gthistlethwaite
Frequent Visitor
8 years ago

Thanks Anonymous,

 

I will try that for future reference, just not a nice original dataset to work with I'm afraid, the source is a SharePoint List.#

 

Cheers,

Gary