Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 !!
Proud to be a PBI Community Champion
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 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
@Anonymous Please create a "New Measure" as below:
CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])
Proud to be a PBI Community Champion
@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
@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 !!
Proud to be a PBI Community Champion
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |