Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
4 years ago
Solved

Grand total Percentage dynamically

Hi All,

I need to calculate the percentage of Total. If no filter is applied it should calculate based on grand total. If i apply any filter say country, manager name then it should take the total based on the filter. I need this to be dynamically.

MonthEmpidLeader nameCountry
Jan-1-2021123JackCanada
Jan-1-2021123JackCanada
Jan-1-2021123JackCanada
Jan-1-2021234MickCanada
Jan-1-2021234MickCanada
Feb-1-2021123JackCanada
Feb-1-2021123JackCanada
Feb-1-2021123JackCanada
Feb-1-2021234MickCanada
Feb-1-2021234MickCanada
Feb-1-2021234MickCanada

 

Total is calculated based on the count of Empid.

so when i make the selection as country = Canada, leader name = Jack then i need GT% = 3/4 = 75 %.

if all filters are cleared the it should be based on to overall total.

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, unnijoy 

     

    Please check the following methods.

    Measure = 
Var _name=SELECTEDVALUE('Table'[Leader name])
Var _CountEmpid=CALCULATE(COUNT('Table'[Empid]), FILTER(ALL('Table'),MONTH('Table'[Month])=1))
Var _Countname=CALCULATE(COUNT('Table'[Leader name]),FILTER(ALL('Table'),MONTH('Table'[Month])=1&&[Leader name]=_name))
Return
IF(_name=BLANK(),_CountEmpid,DIVIDE(_Countname,_CountEmpid))

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

6 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, unnijoy 

     

    Please check the following methods.

    Measure = 
Var _name=SELECTEDVALUE('Table'[Leader name])
Var _CountEmpid=CALCULATE(COUNT('Table'[Empid]), FILTER(ALL('Table'),MONTH('Table'[Month])=1))
Var _Countname=CALCULATE(COUNT('Table'[Leader name]),FILTER(ALL('Table'),MONTH('Table'[Month])=1&&[Leader name]=_name))
Return
IF(_name=BLANK(),_CountEmpid,DIVIDE(_Countname,_CountEmpid))

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      4 years ago

      hi v-zhangti ,

       

      Thanks for the help. the measure is not gving me the value that am looking for. Let me explain it.

      I am using some slicer as filter. Month-date,Leader name,Country.

      I am calculating the sales total based on the count of Empid. And i am taking the running total.

      Sales total = calculate(count(table[Empid],DATESYTD(Table[month],"31/12")

      Am using  Sales total as Numerator for calulating the  Sales %.

      For denominator i need to chek the below things.

       

      1. If no fliter is selected then the denominator should be the total count of Empid.

      2. If we selcet any team leader name then Numeratoe will be his Empid count and denominator should be the total Emp id of the country he belong to. 

      MonthEmpidLeader nameCountry
      Jan-1-2021123JackCanada
      Jan-1-2021123JackCanada
      Jan-1-2021123JackCanada
      Jan-1-2021234MickCanada
      Jan-1-2021234MickCanada
      Jan-1-2021567NickUS
      Jan-1-2021567NickUS
      Jan-1-2021567NickUS
      Jan-1-2021876RoseUS
      Jan-1-2021876RoseUS
      Jan-1-2021876RoseUS
      Jan-1-2021876RoseUS
      Jan-1-2021876RoseUS
      Feb-1-2021123JackCanada
      Feb-1-2021123JackCanada
      Feb-1-2021123JackCanada
      Feb-1-2021234MickCanada
      Feb-1-2021234MickCanada
      Feb-1-2021234MickCanada
      Feb-1-2021567NickUS
      Feb-1-2021567NickUS
      Feb-1-2021567NickUS
      Feb-1-2021876RoseUS
      Feb-1-2021876RoseUS
      Feb-1-2021876RoseUS
      Feb-1-2021876RoseUS
      Feb-1-2021876RoseUS

       so as per the above table if am selcting the name Rose then the measure sould be like  10/16

      10 = total count of Rose 

      16 = total Empid count of US.

       rest all the desominator shoud be the taotal Empid count.

       

      for example if i selcet US then the measure should give 16/27

      where 16 = Total Empid count of US

      27 = Overall count of Empid.

       

      How can we achive this.

       

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      4 years ago

      hi v-zhangti ,

       

      Thanks for the help. I made a small change in the dax that you gave me. now it is working. 🙂

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    unnijoy , Not able to get that 3/4 logic. can you explain

     

    you can try a measure like

     

    divide(calculate(count(Table[Empid]), allselected()) ,

    calculate(count(Table[Empid]), all()) )

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      4 years ago

      Hi amitchandak ,

       

      thanks for the qucik response. A small change its 3/5

      3 = total count of Jack for January. 

      5 = overll total empid count.

      i use the formula that u gave but when i put the measure in a bar graph it is taking the overall count of the selected manager. what we need is the overall total. so as ber the above example it should be 3/5...

      how can we achive this.