Help in dax

[Anonymous]

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

[barritown · Accepted solution]

Hi @Anonymous,

I could propose to solve your problem with the measure below. If you have a calendar table, you can simplify the part where "qtr" and "fy" are calculated. Your test case is OK as you can see on the screenshot. 

In plain text for convenience:

Measure = 
VAR Tbl = 
ADDCOLUMNS ( 
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( Ownership[owner] ),
            SUMMARIZE (
                ADDCOLUMNS ( Sales, 
                    "qtr", 
                    VAR _temp = QUARTER ( [date] ) 
                    RETURN "q" & CONVERT ( IF ( _temp = 1, 4, _temp - 1 ), STRING ),
                    "fy",
                    VAR y = YEAR ( [date] )
                    VAR res = IF ( MONTH ( [date] ) IN { 1, 2, 3 }, y, y + 1 )
                    RETURN "fy" & RIGHT ( CONVERT ( res, STRING ), 2 ) ),
            [fy], [qtr], [product], 
            "total sales",
            SUM (Sales[sales]) ) ),
        "productperc",
        VAR CurrentOwner = [owner]
        VAR CurrentQtr = [qtr]
        VAR CurrentProduct = [product]
        VAR CurrentFY = [fy]
        RETURN MINX ( FILTER ( Ownership, [owner] = CurrentOwner && [fq] = CurrentQtr && [product] = CurrentProduct && [fy] = CurrentFY ), [percentage] ) ),
    "share",
    [total sales] * [productperc] / 100 )
RETURN SUMX ( Tbl, [share] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian