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 🙂
Highlighted
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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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.

Top Solution Authors