Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
Thanks, It worked but with few changes on names 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |