simple calculation based on aggregate value reg,.

[Meerakhanna]

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 possible? - Thank you!

	A	B	C	Total	AB on Total
India	100	70	52	222	76.6%
China	120	30	86	236	63.6%

[Eric_Zhang · Accepted solution]

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

[Meerakhanna]

Eric!

Thanks for that solution. It worked for me 🙂 

[Eric_Zhang]

@Meerakhanna

Great to hear that. 🙂

[BhaveshPatel]

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.

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]

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....

[Meerakhanna]

I think that its not a simple calculation... 😐