Forum Discussion

vishal_singh's avatar
vishal_singh
Frequent Visitor
9 years ago
Solved

conditional percentage using DAX

I have a table with given columns and data:   CustType                     Class           values Active Customers         COM           100671 Active Customers          RES             272733 X...
General Comment
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi vishal_singh,

    Please click the table, Right click->Edit Query, select  Triangle (highlighted in red line) in the Class column field. After order class, the same class will be together as follows.



    Then create calculated columns using the following formulas and get expected result.

    sum of class = CALCULATE(SUM(Table14[values]),ALLEXCEPT(Table14,Table14[Class]))-Table14[values]
    Percentage = IF(Table14[CustType]="Active Customers",1,DIVIDE(Table14[values],Table14[sum of class]))




    If you have any other question, please let me know.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi vishal_singh,

Please click the table, Right click->Edit Query, select  Triangle (highlighted in red line) in the Class column field. After order class, the same class will be together as follows.



Then create calculated columns using the following formulas and get expected result.

sum of class = CALCULATE(SUM(Table14[values]),ALLEXCEPT(Table14,Table14[Class]))-Table14[values]
Percentage = IF(Table14[CustType]="Active Customers",1,DIVIDE(Table14[values],Table14[sum of class]))




If you have any other question, please let me know.

Best Regards,
Angelia

vishal_singh's avatar
vishal_singh
Frequent Visitor
9 years ago

Thanks, It worked but with few changes on names :)