I have two tables in Power BI: "Ownership" and "Sales". The "Ownership" table contains the product-wise ownership percentages by owner, while the "Sales" table contains information about the sales, including the date and product. The ownership percentages vary each quarter, and I need to calculate the share by owner based on these changing percentages.
Table: Ownership
| product | owner | fy | fq | percentage |
| x | dattu | fy24 | q1 | 50 |
| x | dattu | fy24 | q2 | 70 |
| x | sanket | fy24 | q1 | 50 |
| x | sanket | fy24 | q2 | 30 |
| y | dattu | fy24 | q1 | 30 |
| y | dattu | fy24 | q2 | 70 |
| y | sanket | fy24 | q1 | 70 |
| y | sanket | fy24 | q2 | 30 |
Table: Sales
| date | product | sales |
| 03-04-2023 | x | 10 |
| 04-04-2023 | y | 20 |
| 05-04-2023 | x | 30 |
| 06-04-2023 | y | 40 |
| 07-04-2023 | x | 50 |
| 08-04-2023 | y | 60 |
| 09-04-2023 | x | 70 |
| 10-04-2023 | y | 80 |
| 03-05-2023 | x | 90 |
| 04-05-2023 | y | 100 |
| 05-05-2023 | x | 110 |
| 06-05-2023 | y | 120 |
| 07-05-2023 | x | 130 |
| 05-07-2023 | y | 140 |
| 06-07-2023 | x | 150 |
| 07-07-2023 | y | 160 |
| 08-07-2023 | x | 170 |
| 09-07-2023 | y | 180 |
| 10-07-2023 | x | 190 |
| 11-07-2023 | y | 200 |
| 12-07-2023 | x | 210 |
| 13-07-2023 | y | 220 |
Our financial cycle starts on April 1st and ends on March 31st. Each financial quarter consists of three months. For example, financial Q1 includes April, May, and June.
I would like to create a report with a date filter so that when the user selects a date range, the sales data is filtered accordingly. For example, if the user selects the date range from 10/4/23 to 9/7/23, the sales data should be filtered as shown below:
Table: Filtered Sales
| date | product | sales |
| 10-04-2023 | y | 80 |
| 03-05-2023 | x | 90 |
| 04-05-2023 | y | 100 |
| 05-05-2023 | x | 110 |
| 06-05-2023 | y | 120 |
| 07-05-2023 | x | 130 |
| 05-07-2023 | y | 140 |
| 06-07-2023 | x | 150 |
| 07-07-2023 | y | 160 |
| 08-07-2023 | x | 170 |
| 09-07-2023 | y | 180 |
Based on the ownership percentages and filtered sales data, I would like to create a final report as a table visual in Power BI.
Table: Final Report
| Owner | Total |
| sanket | 615 |
| dattu | 815 |
The calculation for the final report can be understood from the table below:
Table: Calculation Details
| owner | fy | fq | product | perc | total(ignore owner) | share |
| dattu | fy24 | q1 | x | 50 | 330 | 165 |
| sanket | fy24 | q1 | x | 50 | 330 | 165 |
| dattu | fy24 | q1 | y | 30 | 300 | 90 |
| sanket | fy24 | q1 | y | 70 | 300 | 210 |
| dattu | fy24 | q2 | x | 70 | 320 | 224 |
| sanket | fy24 | q2 | x | 30 | 320 | 96 |
| dattu | fy24 | q2 | y | 70 | 480 | 336 |
| sanket | fy24 | q2 | y | 30 | 480 | 144 |
In the "Calculation Details" table, I have computed the share by owner based on the ownership percentages and total sales (ignoring the owner). For each owner, financial year (FY), financial quarter (FQ), and product combination, I calculated the share using the following formula:
Share = Total (Ignore Owner) * Percentage
Hi super users, Please kindly help stuck with 15 days
@amitchandak @Greg_Deckler @tamerj1 @johnt75 @Jihwan_Kim
