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!
Go to Solution.
@MonicaElizondo 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 !!
Proud to be a Super User!
View solution in original post
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:
@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 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.
@MonicaElizondo Please create a "New Measure" as below:
CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])
@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.
@PattemManohar thanks a lot for your help.
It works perfect for me.
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.