Calculating Share of Sales by Owner in Power BI with Changing Ownership Percentages

[Anonymous]

I am working on a sales analysis report where I have two tables: "Ownership" and "Sales." The "Ownership" table contains information about the percentage ownership of products by different owners, while the "Sales" table contains details about the sales transactions.

The "Ownership" table has the following columns: product, owner, financial year (fy), financial quarter (fq), and percentage. Here's a sample:

product owner fy fq percentage

x	dattu	24	q1	50
x	dattu	24	q2	70
x	san	24	q1	50
x	san	24	q2	30
y	dattu	24	q1	30
y	dattu	24	q2	70
y	san	24	q1	70
y	san	24	q2	30

The "Sales" table contains columns for date, product, and sales amount. Here's a sample:

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
...	...	...

Our financial cycle starts on April 1st and ends on March 31st. Each quarter consists of three consecutive months. For example, financial quarter 1 (fq1) includes April, May, and June.

I need to create a report where users can filter sales based on a specific date range. For instance, if a user selects a date range from 10/4/23 to 9/7/23, the sales should be filtered accordingly. Here's an example of the filtered sales data:

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
...	...	...

Now, I want to create a table visualization in Power BI to show the share of sales by owner based on the changing ownership percentages for the selected date range. The desired output should be as follows:

Owner Total Sales

dattu	815
sanket	615

I have attempted to calculate this using DAX measures, but I'm facing challenges in incorporating the changing ownership percentages for daterange.

@amitchandak please help

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Total Sales =
SUMX (
    'Ownership',
    [percentage] * 0.01
        * SUMX (
            FILTER (
                'Sales',
                [date]
                    >= IF (
                        VALUE ( RIGHT ( [fq], 1 ) ) <= 3,
                        DATE ( 2000 + [fy] - 1, VALUE ( RIGHT ( [fq], 1 ) ) * 3 + 1, 1 ),
                        DATE ( 2000 + [fy], 1, 1 )
                    )
                    && [date]
                        <= IF (
                            VALUE ( RIGHT ( [fq], 1 ) ) <= 3,
                            EOMONTH ( DATE ( 2000 + [fy] - 1, VALUE ( RIGHT ( [fq], 1 ) ) * 3 + 3, 1 ), 0 ),
                            DATE ( 2000 + [fy], 3, 31 )
                        )
                    && [product] = EARLIER ( Ownership[product] )
            ),
            [sales]
        )
)

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 

please try

AcumuladoAnterior =
CALCULATE (
Con_DetalleRC_ALL[Debe-Haber],
FILTER ( ALL ( Fechas ), Fechas[Fecha] <= MAX ( Fechas[Fecha] ) )
)