Product of row values

[Anonymous]

Hi All,

I have a table (sample of few rows of dummy data as I am unable to share actual data owing to confidentiality), for which I need to calculate product across rows:

Company	Segment	Year	Share	% Contribution	Share * % Contribution
A	X1	2019	0.55	65%	0.36
A	X2	2019	0.35	35%	0.12
B	X1	2019	0.30	77%	0.23
B	X2	2019	0.14	23%	0.03
C	X1	2019	0.10	100%	0.10

I want to calculate weighted share using Share * % Contribution (measure). The result look like this in my excel:

Company	Segment	Year	Share	% Contribution	Share * % Contribution	weighted share
A	X1	2019	0.55	65%	0.36	0.043794
A	X2	2019	0.35	35%	0.12	0.043794
B	X1	2019	0.30	77%	0.23	0.007438
B	X2	2019	0.14	23%	0.03	0.007438
C	X1	2019	0.10	100%	0.10	0.10

However, my results are totally different in PBI. Currently I am using the following formula:

CALCULATE(PRODUCTX(Sheet 1,[Share * % Contribution]), ALLEXCEPT(Sheet1,Sheet1[Company],Sheet1[Year]))

Ideally the formula should be multiplying as follows:

• 0.36 and 0.12 for company A
• 0.23 and 0.03 for company B
• 0.1 remains same for Company C

Can you guide me as to what i am doing wrong? Thank you for all help in advance!!! 

Regards,

Kaushik

[AlexAlberga727]

The issue you're having is that Power BI uses Row Context, and your formulas are looking at your values as being unique due to the segment value.

I'm fairly new with Power BI as well and would like to hear a solution as I am unfamiliar with all the different filter expresressions.

However, If you dont mind showing this value seperately you can do so very easily without any complicated DAX expressions.

Create another table showing only the Company and Share*%Cont value -

[Anonymous]

@AlexAlberga727  I would be needing the coloumns for further calculations hence creating another table would make the sheet heavy, and since its a tool to be used by other people, it may complicate things for them. Hence would like to keep all calculations in one table. 

[Anonymous · Accepted solution]

My 2 cents. I haven't looked at the formula but regardless of them, THIS table is conceptually questionable:

You DON'T show the same value twice filtered by two different segments. Weighted Share is a value that is the same on two different rows and its' depending on another row of data.

The easiest way would be to have these measures:

ShareContribution = your formula to calculate share
ShareContributionX1 = CALCULATE (ShareContribution; YourTable[Segment]=X1) <--- so the same formula applied to a different filter

ShareContributionX2 = CALCULATE (ShareContribution; YourTable[Segment]=X2) 

Now that you have both shares you just puth BOTH of them in two columns (so you have ONE row per company, not two) and then you simply do ShareContributinX1*ShareContributionX2

Company	ShareX1	ShareX2	ShareTotal
A	...	...	X1*X2

If you really want to show a Segment hierarchy (so one line per segment per company) you don't use in that visual a metric that is not working on the relevant dimensions.

[Anonymous]

Oh, and ALLEXCEPT is not an easy filter modifier to use....

[AlexAlberga727]

@amitchandakThanks for the provided example. Internesting solution. Had to wrap my head around how those functions work. thanks!

[amitchandak]

Please refer to the solution, I created both as measure and column:

https://www.dropbox.com/s/ov8jyccte4bl57z/productX.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Thanks for your reply @amitchandak 

When I tried implementing the solution it gives me errors. Apprecite your patience and help. 

Regards,

Kaushik