Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Can you guide me as to what i am doing wrong? Thank you for all help in advance!!!
Regards,
Kaushik
Solved! Go to 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.
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 -
@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.
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.
Oh, and ALLEXCEPT is not an easy filter modifier to use....
@amitchandakThanks for the provided example. Internesting solution. Had to wrap my head around how those functions work. thanks!
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
Thanks for your reply @amitchandak
When I tried implementing the solution it gives me errors. Apprecite your patience and help.
Regards,
Kaushik
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |