Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |