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 b...
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.

     

     

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. 🙂