Forum Discussion

Meerakhanna's avatar
Meerakhanna
Helper I
9 years ago
Solved

simple calculation based on aggregate value reg,.

Hi, I surprise if i can do a calculation over an aggregated value in power BI. Please see to the below screen shot & table. I need that 'AB on Total' value to be calculated on run in Power BI. Is it...
Need Help
Show and Tell
Tips and Tricks
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Meerakhanna

     

    If you have data as below, you would first pivot the table.

     

    Then create and apply two measures in a Matrix visual.

     

    Total = SUM([A]) +SUM([B])+SUM([C])

AB on Total = (SUM([A])+SUM([B]))/[Total]

     

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

Hi There,

 

You can use the newly added functionality Invoke Custom Function in the Query Editor for this and It would be more creative solution as you can use them for other type of similar tasks as well.

 

To do so, 

 

1.Add a Blank query and Write the below code:

(Qscore as number, Rscore as number,Sscore as number) => Qscore + Rscore + Sscore

 

Note: Change the Code as per your requirement as your calculation are different than shown here.

 

M11.PNGM10.PNGM12.PNGM13.PNG

 

 

2. Go to your original query and Under the Add Column Tab, Select Invoke Custom Function.

and Choose the appropriate options and it will give you desire results. 

 

 

                                                                        OR

 

You can use custom column in the query editor to achive the desired results.

 

Let me know if you get stuck.

  • Meerakhanna's avatar
    Meerakhanna
    Helper I
    9 years ago

    Hi Bhavesh!

    Thanks for the reply.

    But the thing is I 've the data as unpivoted column values. I had made that as matrix so as to show up my requirement. 

    I'll have to have some kind of work around anyway....I m still working on :( 

    I am not that good with the language used here so facing some issue with writing formulae....