topic Re: simple calculation based on aggregate value reg,. in Developer

simple calculation based on aggregate value reg,.

Meerakhanna — Thu, 06 Oct 2016 06:06:55 GMT

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%

Re: simple calculation based on aggregate value reg,.

Meerakhanna — Thu, 06 Oct 2016 11:50:44 GMT

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

Re: simple calculation based on aggregate value reg,.

BhaveshPatel — Thu, 06 Oct 2016 12:05:38 GMT

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.

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

Let me know if you get stuck.

Re: simple calculation based on aggregate value reg,.

Meerakhanna — Thu, 06 Oct 2016 15:10:53 GMT

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

Re: simple calculation based on aggregate value reg,.

Eric_Zhang — Fri, 07 Oct 2016 08:30:49 GMT

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

Re: simple calculation based on aggregate value reg,.

Meerakhanna — Wed, 26 Oct 2016 09:00:31 GMT

Eric!

Thanks for that solution. It worked for me 🙂

Re: simple calculation based on aggregate value reg,.

Eric_Zhang — Wed, 26 Oct 2016 09:02:02 GMT

@Meerakhanna

Great to hear that. 🙂