cancel
Showing results for
Did you mean:
Frequent Visitor

## Product of row values

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

## Re: Product of row values

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.

Proud to be a Super User!

7 REPLIES 7
Super User IV

## Re: Product of row values

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

In case it does not help, please provide additional information and mark me with @
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 Super User! Linkedin

Resolver I

## Re: Product of row values

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 -

Resolver I

## Re: Product of row values

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

Frequent Visitor

## Re: Product of row values

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

Regards,

Kaushik

Frequent Visitor

## Re: Product of row values

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

Super User I

## Re: Product of row values

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.

Proud to be a Super User!

Super User I

## Re: Product of row values

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

Proud to be a Super User!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors