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

PattemManohar's avatar
PattemManohar
Community 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
    Community 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 !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      PattemManohar thanks a lot for your help.

       

      It works perfect for me.

       

      Thanks again

      Monica