Grouping values in a column then multiply

[icdns]

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!

[Anonymous · Accepted 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! 

[orlandom]

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:

Groupby calculation using both a calculated column and a measure

Hope this helps!
Orlando Mézquita

[icdns]

Clear representation of the last table:

[amitchandak]

@icdns , try like

FINAL_PERCENTAGE= PRODUCTX(filter(Table,[SERVICE]=earlier([SERVICE])),[PERCENTAGE])

[icdns]

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%

[Anonymous · Accepted 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! 

[icdns]

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%

[v-xicai · Accepted solution]

Hi @icdns ,

1. How can I create a column to group the Apps by Service.
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%) 

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.