Countx with some conditions

[Anonymous]

Hello!!

Im trying to do this in a calculated column in dax, im not sure where is the mistake.

I have a table with information like this:

I want to count by country, supplier and mat_group; im trying with this dax expresion, for test the supplier and the mat_group, but I dont have the result that I expect.

Count = COUNTX(FILTER('Mytable','Mytable'[SUPPLIER]=EARLIER('Mytable'[SUPPLIER]) && 'Mytable'[MAT_GROUP]=EARLIER('Mytable'[MAT_GROUP])),'Mytable'[MAT_GROUP])

I actually received the count of the actual count, but I expected the column expect count.

I hope someone could help me!

Greetings

Mónica

[MarkLaf]

This is a pretty simple calculated column, especially if you only have COUNTRY, SUPPLIER, and MAT_GROUP columns:

Count = CALCULATE(COUNTROWS(Mytable))

If you do have other columns, then use this formula:

Count = CALCULATE(COUNTROWS(Mytable),ALLEXCEPT(Mytable,Mytable[COUNTRY],Mytable[SUPPLIER],Mytable[MAT_GROUP])) 

I'll also note that you can get this in the report layer with a few drag and drops - click the table visual, drag in all columns, and then drag in a duplicate column and set to count:

[Anonymous]

@MarkLaf thanks for your reply; 

Maybe I dont can explain me, Im looking when I filter country and supplier show me the count of diferentes mat_groups, for example

Country   Supplier       Mat_group

US               A                  123

US               A                  123

US               A                  456

US               B                   123

When I filter US and Supplier A, I want to see in count column 2 (the two diferent mat_group) and when y filter supplier B i want to see count 1 (mat_group 123)

I have more columns in the table, almost 150 

I dont use the solution in the report layer, because with the information im going to do another calculations before show in the report layer.

Thanks again

Monica

[PattemManohar]

@Anonymous Please create a "New Measure" as below:

CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])

[Anonymous]

@PattemManohar thanks a lot for you reply.

Is possible to do this in a column? I want to have the value in a column because I need for another calcutations.

I tried this solution in a column but it doesnt work that I want.

Thanks Again

Monica

[PattemManohar · Accepted solution]

@Anonymous Please try below using "New Column"

ActualCount = CALCULATE(COUNTROWS(MatGroups),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))

ExpectCount = CALCULATE(DISTINCTCOUNT(MatGroups[MatGroup]),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))

Note - You can solve the same in "Power Query" as well using "GROUP BY" option..

Hope this helps !!

[Anonymous]

@PattemManohar thanks a lot for your help.

It works perfect for me.

Thanks again

Monica