conditional percentage using DAX

[vishal_singh]

I have a table with given columns and data:

CustType                     Class           values

Active Customers         COM           100671

Active Customers          RES             272733

XXXXXXXXXXXXX          XXXXX          XXXXXX

XXXXXXXXXXXXX          XXXXX          XXXXXXX

XXXXXXXXXXXXX          XXXXX          XXXXXXX

Mobile No                     COM           45362

EMAIL ID                        RES             142362

XXXXXXXXXXXXX           XXXX                 XXXXXX

XXXXXXXXXXXXX          XXXXX               XXXXXXX

XXXXXXXXXXXXX          XXXXX               XXXXXXX

Now I want to write a DAX, such that I can add a new column with values Percentage corresponding to each customer class such as

CustType                     Class           values             Percentage

Active Customers         COM           100671             100

Active Customers          RES             272733             100

XXXXXXXXXXXXX          XXXXX          XXXXXX

XXXXXXXXXXXXX          XXXXX          XXXXXXX

XXXXXXXXXXXXX          XXXXX          XXXXXXX

Mobile No                     COM           45362             45.05     (45362/100671)%

EMAIL ID                        RES             142362           52.19     (142362/272733)%

XXXXXXXXXXXXX           XXXX                 XXXXXX

XXXXXXXXXXXXX          XXXXX               XXXXXXX

XXXXXXXXXXXXX          XXXXX               XXXXXXX

Please help.

[Baskar]

Here u have to create calculated Column that will give the expected result .

Column = var cur_class = Class

var Max_values = calculate(Max(Values), filter(Table_Name, Class=cur_class)

return  values / Max_Values

Note : Bold letters u have replace your related names

once create the column change the Format to Percentage  under in modelling screen ..

It will help u , if not let me know

[austinsense]

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]

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]

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

[v-huizhn-msft · Accepted solution]

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]

Thanks, It worked but with few changes on names 🙂