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.
Hello,
Would really need your help in grouping values in a column then multiply it's percentage. For example, in my below table I have APPNAME and PERCENTAGE columns:
APPNAME | PERCENTAGE |
App1 | 98.99% |
App2 | 98.88% |
App3 | 100.00% |
App4 | 98.54% |
App2 | 98.88% |
App5 | 100.00% |
App1 | 98.99% |
App3 | 100.00% |
App4 | 98.54% |
App2 | 99.04% |
App1 | 99.02% |
App2 | 98.88% |
App3 | 100.00% |
I wanted to have a 2 columns which will group the values of the APPNAME then multiply the values to come up with a FINAL_PERCENTAGE column. This is the desired output:
Please take note that a one Services can have the same APPNAME (Ex. App1)
Example:
Service A has 2 APPNAME (App 1 & App 2)
Final Percentage = App1 (98.99) x App2 (98.88)
SERVICE | APPNAME | PERCENTAGE | FINAL_PERCENTAGE |
Service A | App1 | 98.99% | 97.88% |
Service A | App2 | 98.88% | 97.88% |
Service B | App3 | 100.00% | 97.44% |
Service B | App4 | 98.54% | 97.44% |
Service B | App2 | 98.88% | 97.44% |
Service B | App5 | 100.00% | 97.44% |
Service C | App1 | 98.99% | 96.61% |
Service C | App3 | 100.00% | 96.61% |
Service C | App4 | 98.54% | 96.61% |
Service C | App2 | 99.04% | 96.61% |
Service D | App1 | 99.02% | 97.91% |
Service D | App2 | 98.88% | 97.91% |
Service D | App3 | 100.00% | 97.91% |
Thank you so much!
Solved! Go to Solution.
Hello @icdns,
Please try below code :
CalculatedFinal% =
VAR _Service= 'Table'[SERVICE]
VAR _Result=PRODUCTX(filter('Table','Table'[SERVICE]=_Service),'Table'[PERCENTAGE])
Return
_Result
Thanks!
Hi @icdns ,
For the two demands, you may create a calculated column like DAX below to get [FINAL PERCENTAGE], then put the column [SERVICE] , [APPNAME] , [PERCENTAGE] and new created column [FINAL PERCENTAGE] into Table visual, sort the table visual by [SERVICE], in this way, it will group the Apps by Service.
FINAL PERCENTAGE =
VAR _Service='Table1'[SERVICE]
RETURN
PRODUCTX(FILTER('Table1','Table1'[SERVICE]=_Service),'Table1'[PERCENTAGE])
In addition, you may select on this column, go to Column Tool, set it as Percent format , and set decimal places.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @icdns ,
If you want to add the column as part of the table in the data model you can use the calculated column proposed by @Anonymous.
Another approach would be to create a measure that will calculate the Final Percentage by Service and can be placed on any visualization. It will also give an overall percentage for all services when placed on a table/matrix.
To create the measure, go to Home -> New measure and enter the following formula:
Final Percentage Measure =
CALCULATE(PRODUCT('Table'[Percentage]), REMOVEFILTERS('Table'[App]))
Here's an image with the results of both approaches:
Hope this helps!
Orlando Mézquita
Clear representation of the last table:
@icdns , try like
FINAL_PERCENTAGE= PRODUCTX(filter(Table,[SERVICE]=earlier([SERVICE])),[PERCENTAGE])
Hi @amitchandak ,
How can I group my AppName by service?
SERVICE | APPNAME | PERCENTAGE | FINAL_PERCENTAGE |
Service A | App1 | 98.99% | 97.88% |
Service A | App2 | 98.88% | 97.88% |
Service B | App3 | 100.00% | 97.44% |
Service B | App4 | 98.54% | 97.44% |
Service B | App2 | 98.88% | 97.44% |
Service B | App5 | 100.00% | 97.44% |
Service C | App1 | 98.99% | 96.61% |
Service C | App3 | 100.00% | 96.61% |
Service C | App4 | 98.54% | 96.61% |
Service C | App2 | 99.04% | 96.61% |
Service D | App1 | 99.02% | 97.91% |
Service D | App2 | 98.88% | 97.91% |
Service D | App3 | 100.00% | 97.91% |
Hello @icdns,
Please try below code :
CalculatedFinal% =
VAR _Service= 'Table'[SERVICE]
VAR _Result=PRODUCTX(filter('Table','Table'[SERVICE]=_Service),'Table'[PERCENTAGE])
Return
_Result
Thanks!
Hello,
1. How can I create a column to group the Apps by Service. For example:
SERVICE | APPNAME | PERCENTAGE |
Service A | App1 | 98.99% |
Service A | App2 | 98.88% |
Service B | App3 | 100.00% |
Service B | App4 | 98.54% |
Service B | App2 | 98.88% |
Service B | App5 | 100.00% |
Service C | App1 | 98.99% |
Service C | App3 | 100.00% |
Service C | App4 | 98.54% |
Service C | App2 | 99.04% |
2. Next, after I group the the apps by service column. How will I create the FINAL PERCENTAGE column? (Formula is just 98.99% x 98.88%)
It's just multiplying the percentage of App1 and App2
SERVICE | APPNAME | PERCENTAGE | FINAL_PERCENTAGE |
Service A | App1 | 98.99% | 97.88% |
Service A | App2 | 98.88% | 97.88% |
Hi @icdns ,
For the two demands, you may create a calculated column like DAX below to get [FINAL PERCENTAGE], then put the column [SERVICE] , [APPNAME] , [PERCENTAGE] and new created column [FINAL PERCENTAGE] into Table visual, sort the table visual by [SERVICE], in this way, it will group the Apps by Service.
FINAL PERCENTAGE =
VAR _Service='Table1'[SERVICE]
RETURN
PRODUCTX(FILTER('Table1','Table1'[SERVICE]=_Service),'Table1'[PERCENTAGE])
In addition, you may select on this column, go to Column Tool, set it as Percent format , and set decimal places.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |