Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
icdns
Post Patron
Post Patron

Grouping values in a column then multiply

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: 

 

APPNAMEPERCENTAGE
App198.99%
App298.88%
App3100.00%
App498.54%
App298.88%
App5100.00%
App198.99%
App3100.00%
App498.54%
App299.04%
App199.02%
App298.88%
App3100.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) 

 

SERVICEAPPNAMEPERCENTAGEFINAL_PERCENTAGE
Service AApp198.99%97.88%
Service AApp298.88%97.88%
Service BApp3100.00%97.44%
Service BApp498.54%97.44%
Service BApp298.88%97.44%
Service BApp5100.00%97.44%
Service CApp198.99%96.61%
Service CApp3100.00%96.61%
Service CApp498.54%96.61%
Service CApp299.04%96.61%
Service DApp199.02%97.91%
Service DApp298.88%97.91%
Service DApp3100.00%97.91%

 

Thank you so much!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hello @icdns,

 

Please try below code :

CalculatedFinal% = 
VAR _Service= 'Table'[SERVICE]
VAR _Result=PRODUCTX(filter('Table','Table'[SERVICE]=_Service),'Table'[PERCENTAGE])
Return
_Result

 

Thanks! 

View solution in original post

v-xicai
Community Support
Community Support

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])

170.png

 

 

 

In addition, you may select on this column, go to Column Tool, set it as Percent format , and set decimal places.

171.PNG

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.

View solution in original post

7 REPLIES 7
orlandom
Advocate I
Advocate I

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 measureGroupby calculation using both a calculated column and a measure

Hope this helps!
Orlando Mézquita

icdns
Post Patron
Post Patron

Clear representation of the last table:

 

icdns_0-1597400128095.png

 

@icdns , try like

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

Hi @amitchandak , 

 

How can I group my AppName by service? 

 

SERVICEAPPNAMEPERCENTAGEFINAL_PERCENTAGE
Service AApp198.99%97.88%
Service AApp298.88%97.88%
Service BApp3100.00%97.44%
Service BApp498.54%97.44%
Service BApp298.88%97.44%
Service BApp5100.00%97.44%
Service CApp198.99%96.61%
Service CApp3100.00%96.61%
Service CApp498.54%96.61%
Service CApp299.04%96.61%
Service DApp199.02%97.91%
Service DApp298.88%97.91%
Service DApp3100.00%97.91%
Anonymous
Not applicable

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: 

 

SERVICEAPPNAMEPERCENTAGE
Service AApp198.99%
Service AApp298.88%
Service BApp3100.00%
Service BApp498.54%
Service BApp298.88%
Service BApp5100.00%
Service CApp198.99%
Service CApp3100.00%
Service CApp498.54%
Service CApp299.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 

 

SERVICEAPPNAMEPERCENTAGEFINAL_PERCENTAGE
Service AApp198.99%97.88%
Service AApp298.88%97.88%

 

v-xicai
Community Support
Community Support

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])

170.png

 

 

 

In addition, you may select on this column, go to Column Tool, set it as Percent format , and set decimal places.

171.PNG

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.