Forum Discussion
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.
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
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
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,
AngeliaThanks, It worked but with few changes on names :)
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
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
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
