Forum Discussion

icdns's avatar
icdns
Icon for Post Patron rankPost Patron
6 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello icdns,

     

    Please try below code :

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

     

    Thanks! 

  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

7 Replies

  • icdns's avatar
    icdns
    Icon for Post Patron rankPost Patron
    6 years ago

    Clear representation of the last table:

     

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      icdns , try like

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

      • icdns's avatar
        icdns
        Icon for Post Patron rankPost Patron
        6 years ago

        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%
  • orlandom's avatar
    orlandom
    Icon for Advocate I rankAdvocate I
    6 years ago

    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