Forum Discussion

naseem_1973's avatar
naseem_1973
Icon for Helper I rankHelper I
3 years ago
Solved

%age require a text coulmn

Dear All,

 

I have a text-based column consisting of different categories of calls in my table. I want to calculate the percentage of each category in this column.

I've tried using distinct count and regular count, but the results are not accurate.

 

Need Help
  • eliasayyy's avatar
    eliasayyy
    3 years ago

    Cout = COUNTROWS('Table')
    Count All = COUNTROWS('ALL('Table')
    Count Per = DIVIDE([Count],[Count All])

  • Prateek97's avatar
    Prateek97
    3 years ago

    Hi naseem_1973 ,

     

    Hope this works for you.

     

    Measure = COUNT(CTable[Category])/CALCULATE(COUNT(CTable[Category]),ALL(CTable[Category]))

     

     

  • mlsx4's avatar
    mlsx4
    3 years ago

    Okey, I have just realized why!!!

     

    You are using a var as the name of the function. Var is used to store values (https://learn.microsoft.com/en-us/dax/var-dax).  Take a look at my code, the name is % of communications. I will paste it again:

     

     

    % of communications = 

var allValues= CALCULATE([Total communications],ALLSELECTED(MyTable[Communication Method]))
return DIVIDE([Total communications],allValues,0)

     


    BTW, variables are really useful in Power BI to improve readability and performance sometimes (https://learn.microsoft.com/en-us/dax/best-practices/dax-variables). 

8 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi naseem_1973 

     

    Try this:

     

    Total communications = COUNTROWS(MyTable)
    % of communications = 

var allValues= CALCULATE([Total communications],ALL(MyTable))
return DIVIDE([Total communications],allValues,0)

     

    If you want to be able to filter and keep being 100%, use this:

    % of communications = 

var allValues= CALCULATE([Total communications],ALLSELECTED(MyTable[Communication Method]))
return DIVIDE([Total communications],allValues,0)

     

      • mlsx4's avatar
        mlsx4
        Icon for Memorable Member rankMemorable Member
        3 years ago

        Hi naseem_1973 

         

        I don't know why, because for me it's working perfectly. If you select some of the values:

         

        If there's nothing selected:

         

        In fact, my solution also considers the possibility of catching errors while dividing

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Cout = COUNTROWS('Table')
    Count All = COUNTROWS('ALL('Table')
    Count Per = DIVIDE([Count],[Count All])

  • Prateek97's avatar
    Prateek97
    Icon for Resolver III rankResolver III
    3 years ago

    Hi naseem_1973 ,

     

    Hope this works for you.

     

    Measure = COUNT(CTable[Category])/CALCULATE(COUNT(CTable[Category]),ALL(CTable[Category]))