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
I am trying to recreate an excel calculation in Power BI.
In excel - I have 2 columns denpendent on each other in the calculations.
"Adjusted total Shares" is dependent on column "Total Shares". AND "Total Shares" column is dependent on previous value of "Adjested Total Shares". Please refer to the Excel formula in the attached data link.
Data File: https://drive.google.com/open?id=1YdxP-yv9C7tP3B1zo3tIvs8GCFWr-6M2
I am trying to recreate these 2 as measure/calculated columns in Power BI.
Any help to solve this issue would be appreciated!!
Thanks
Hi @sv12 ,
What is the source table structure? Are "Adjusted total Shares" and "Total Shares" calculated based on original feilds? Please provide more description about the calculation regular with examples.
Best regards,
Yuliana Gu
@v-yulgu-msft Thank you for your response.
Below, the Red Highlighted part is the source data. Black Highlighted are to be calculated.
The formula for Total Shares in Excel:
If( Date = 8/10/1988, 200000, Income + Cap Gain+ Previous Values of Adjusted Total Shares)
Formula for Adjusted Total Shares:
Total Shares + Withdrawn shares
There is a circular dependancy on these 2 columns.
Date | Income | Cap Gain | WITHDRAWN SHARES | TOTAL SHARES | ADJUSTED TOTAL SHARES |
8/10/1988 | 0.00 | 200,000.000 | 200,000.000 | ||
9/31/1988 | 0.000 | 0.000 | (826.72) | 200,000.000 | 199,173.280 |
9/30/1988 | 0.000 | 0.000 | (799.74) | 199,173.280 | 198,373.536 |
10/31/1988 | 0.000 | 0.000 | (784.68) | 198,373.536 | 197,588.853 |
11/30/1988 | 0.000 | 0.000 | (786.16) | 197,588.853 | 196,802.689 |
12/31/1988 | 0.000 | 0.000 | (784.68) | 196,802.689 | 196,018.006 |
1/31/1989 | 3058.273 | 4050.693 | (809.06) | 203,126.972 | 202,317.911 |
2/28/1989 | 0.000 | 0.000 | (823.45) | 202,317.911 | 201,494.459 |
12/24/1996 | 0.000 | 0.000 | (807.49) | 201,494.459 | 200,686.965 |
12/24/1996 | 0.000 | 0.000 | (774.47) | 200,686.965 | 199,912.492 |
With the Red highlighted part as raw data in Power BI, I am trying to replicate these calculations in Power BI.
Please let me know what you think.
Thanks
Hi @sv12 ,
You may try below calculated columns.
TOTAL SHARES = VAR previous_TotalShares = CALCULATE ( SUM ( Sample3[ADJUSTED TOTAL SHARES] ), FILTER ( Sample3, Sample3[Index] = EARLIER ( Sample3[Index] ) - 1 ) ) RETURN IF ( Sample3[Date] = DATE ( 1988, 8, 10 ), 200000, Sample3[Income ] + Sample3[Cap Gain] + previous_TotalShares ) ADJUSTED TOTAL SHARES = 200000 + CALCULATE ( SUM ( Sample3[WITHDRAWN SHARES] ), FILTER ( Sample3, Sample3[Index] <= EARLIER ( Sample3[Index] ) ) )
Best regards,
Yuliana Gu
@v-yulgu-msft Thanks for your response.
The "Withdrawn Shares" data is actually a Measure (with What-if Parameters). I did not mention that to simplify the problem.
But when I try to use the What-If Parameters enabled measure value (Withdrawn Shares), with your code; the values are thrown off because What-if Parameters are not supported in Calculated columns!
Is there a way to modify your code and use it for Measures instead of calculated columns?
Also, in your code: Please see the Red highlighted part. When I try the edit, the same cirlular dependancy error is been shown.
ADJUSTED TOTAL SHARES = 200000 (# this has to be replaced by If(Date = min(Date), 200000, current value of Total Shares)) + CALCULATE ( SUM ( Sample3[WITHDRAWN SHARES] ), FILTER ( Sample3, Sample3[Index] <= EARLIER ( Sample3[Index] ) ) )
Thanks for looking into it!
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |