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.
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
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 |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
60 | |
46 | |
17 | |
12 |