cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

Please help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
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.

1.png

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]))


2.png

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

Best Regards,
Angelia

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
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.

1.png

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]))


2.png

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

Best Regards,
Angelia

View solution in original post

Highlighted
Impactful Individual
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 🙂
Resident Rockstar
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
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 🙂

Helpful resources

Announcements
Upcoming Events

Upcoming Events

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

Upcoming Events

Community Summit North America – Join Online!

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

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

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

Top Solution Authors