Countif Per Row, multiple values as grades

[Unicorn_Tech]

Hello.

I have about 20 fields of free text.  Each field has a corresponding "Grade", which is either Green, Yellow or Red (100000000, 100000001, 100000002). I want a column in Power BI, that will total all the "Greens" for the Row, and a column that will total all the "Reds" for the row, etc.

Ideally, for each row, we would then have totals of Green grades, Red grades, and Yellow grades.

It doesn't look like countx is suitable for this.

I would appreciate advice.  Thank you.

[v-luwang-msft]

Hi @Unicorn_Tech ，

Try like below :

step1, create new table use following dax:

Table2 = CALCULATETABLE(DISTINCT('Table'[Column]))

Step2,then create new column:

Column 2 = IF('Table2'[Column]=100000000,"Green",IF('Table2'[Column]=100000001,"Yellow","Red")) 

Step 3, create the following measure:

Measure2 = var test=CALCULATE(COUNT('Table'[Column]),FILTER(ALL('Table'),'Table'[Column]=MAX(Table2[Column])))    RETURN  test

Measure3 = var test= SUMX(Table2,[Measure2])  return 
IF(HASONEVALUE(Table2[Column 2]),MAX(Table2[Column 2])&"="&test,"Total"&"="&test)

Final get :

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@Unicorn_Tech , can you share the expected output

you can have something like

example

A new column = sumx(Table, [Grade] = earlier([Grade]) , [Value])

A new Measure= sumx(allselected(Table), [Grade] = max([Grade]) , [Value])

[Unicorn_Tech]

My expected output:

I have 20 columns which either have the value of 100000000 or 100000001 or 100000002.

I expect the output to be:

Green = count of all 100000000 that appear within the row, across all columns.
Yellow= countof all 100000001 that appear within the row, across all the columns.
Red = count of all 100000002 that appear within the row, across all columns.

So a sample for a particular row:
Green = 15
Yellow = 2
Red = 3

[Greg_Deckler · Accepted solution]

@Unicorn_Tech You want MC Aggregations: https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

That said, you might considering unpivoting your columns as well as that tends to be a better format for analysis.