cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Countx with some conditions

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:17.JPG

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@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]))

image.png

 

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

 

Hope this helps !!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Resolver IV
Resolver IV

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:

 

image.png

Highlighted

@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

Highlighted

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

 

CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])

image.pngimage.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@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

Highlighted

@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]))

image.png

 

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

 

Hope this helps !!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

@PattemManohar thanks a lot for your help.

 

It works perfect for me.

 

Thanks again

Monica

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors