Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:   I want to count by country, supplier and mat_grou...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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 !!

MarkLaf's avatar
MarkLaf
Icon for Super User rankSuper User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous Please create a "New Measure" as below:

     

    CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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 !!