cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## conditional percentage using DAX

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: conditional percentage using DAX

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

6 REPLIES 6
Microsoft

## Re: conditional percentage using DAX

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

Highlighted
Impactful Individual

## Re: conditional percentage using DAX

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Highlighted
Resident Rockstar

## Re: conditional percentage using DAX

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

Highlighted
Frequent Visitor

## Re: conditional percentage using DAX

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

Highlighted
Microsoft

## Re: conditional percentage using DAX

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

Highlighted
Frequent Visitor

## Re: conditional percentage using DAX

Thanks, It worked but with few changes on names 🙂

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors