PRODUCT Function to calculate only at total level

[Anonymous]

Hi all,

First of all, sorry for my long post but I can't find any easy way to explain my issue.

The business in my company uses a specific PRODUCT calculation in excel that we are replicating in PowerBI without success. Here is how it works in Excel:

The excel PRODUCT function is this: {=PRODUCT(B4:D4+1)-1}. In case you don't know how to add the {} to a formula in excel it's just by writing the formula and instead of press Enter press Ctrl+Sift+Enter.

When I do the same formula in PowerBI this is what I get:

Please ignore the usage of MAXX to add and remove the 1. There is a reason for it. Not important to mention it for this post.

Noticed how at Fund level it works but at Portfolio Level it doesn't? For example the Fund1 of Portfolio1 has 0.365 and 0.378 with a total of 0.881. Exactly the same as the Excel Product Function.

But, the total for Portfolio1 in excel is 2.00 and in PowerBI is 2.482. I know the reason behind the difference:

Excel to calculate the total for Porfolio1 is using the following numbers: PRODUCT(0.689,0.776+1)-1 = 2.000

PowerBI to calculate the total for Portfolio1 is doing this: PRODUCTX(0.365,0.324,0.378,0.398+1)-1 = 2.482. I can replicate the same result in Excel if I use those numbers.

So, what's happening? Well, in Excel I'm using the aggregate of the inner Funds and then apply product function over those total values. In PowerBI I'm applying product function over each and every value in the inner group.

That can be prove to be right with Portfolio3 that only has 1 Fund therefore the Total row and the inner rows are the same.

So, my question is simple...how can I with DAX replicate the same behavior of Excel? How can I force the Product function to calculate base on the total row and not the inner components of it?

Is it even possible?

[Anonymous]

Here is the screenshot of the excel file. I don't know why I can't put it in the same post.

[dax]

Hi jmoraga,

As I know, the PRODUCT function in Excel is work like a1*a2*a3, but in your sample, it seems that the result is not equal to a1*a2*(a3+1)-1. So if possible, could you please explain this to me in  deatils? Then I willhelp you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, 

First of all thank you for taking your time and review my issue.

I'm not expert in PRODUCT fuction so I can't talk about the behind the scene logic. What I do know is that my company has that specific excel function ({=PRODUCT(B4:D4+1)-1}) and because the formula is set row by row then it works fine for them.

Look at this:

What PowerBI is returning is the product for all the 4 underline values (from B6:C7) and what I want is to return me the product for the totals (B5:C5).

As I said in the post, the inner rows (at Fund level) are fine but at Portfolio is wrong because what I need is a DAX that first sum the values (For column B is 0.365+0.324=0.689 and for column C is 0.378+0.398=0.776) and calculate the Product base on those totals only (0.689 and 0.776). 

Here is the PowerBI with sample data so you can play with it: https://drive.google.com/open?id=1tnvtE2UC2jYBVBdBiWCAaDhql8lYZyqT

Let me know if you can't download it or if you need any other clarification. At the end of the day what I need is a DAX formula that for Portfolio1 in total shows me the values for column F:

And again, thank you for help me. Appriciate it.