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

austinsense's avatar
austinsense
Impactful Individual
9 years ago

A couple of things here that are going to cause you problems later ...

 

1. You probably don't want to have the grand totals included in your data

2. You probably want to create a measure for this instead of a calculated column

vishal_singh's avatar
vishal_singh
Frequent Visitor
9 years ago

Perhaps. I tried other solutions but nothing worked easily. I think separating aggregate from detail column was useful idea. It make my job much easier now, without bringing Calculated column

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

    Hi vishal_singh，

    Have you resolved your problem? It's helpful to mark the corresponding solution as mark if you have reslove the issue, or post your solution.

    Best Regards,
    Angelia