cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kaushikbirmiwal
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:

CompanySegmentYearShare% ContributionShare * % Contribution
AX120190.5565%0.36
AX220190.3535%0.12
BX120190.3077%0.23
BX220190.1423%0.03
CX120190.10100%0.10

 

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

CompanySegmentYearShare% ContributionShare * % Contributionweighted share
AX120190.5565%0.360.043794
AX220190.3535%0.120.043794
BX120190.3077%0.230.007438
BX220190.1423%0.030.007438
CX120190.10100%0.100.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
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:

 

image.png

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.





Did I answer your question? Mark my post as a solution!

PLEASE follow these instructions to ask your questions!


Proud to be a Super User!




View solution in original post

7 REPLIES 7
Super User IV
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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


AlexAlberga727 Resolver I
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 -

 

example.png

AlexAlberga727 Resolver I
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!

kaushikbirmiwal
Frequent Visitor

Re: Product of row values

Thanks for your reply @amitchandak 

 

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

 

Regards,

Kaushik

 

kaushikbirmiwal
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
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:

 

image.png

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.





Did I answer your question? Mark my post as a solution!

PLEASE follow these instructions to ask your questions!


Proud to be a Super User!




View solution in original post

Super User I
Super User I

Re: Product of row values

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





Did I answer your question? Mark my post as a solution!

PLEASE follow these instructions to ask your questions!


Proud to be a Super User!




Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors