Help in dax

Question

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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table: Ownership&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;productownerfyfqpercentage&nbsp;&nbsp;xdattufy24q150&nbsp;&nbsp;xdattufy24q270&nbsp;&nbsp;xsanketfy24q150&nbsp;&nbsp;xsanketfy24q230&nbsp;&nbsp;ydattufy24q130&nbsp;&nbsp;ydattufy24q270&nbsp;&nbsp;ysanketfy24q170&nbsp;&nbsp;ysanketfy24q230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table: Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dateproductsales&nbsp;&nbsp;&nbsp;&nbsp;03-04-2023x10&nbsp;&nbsp;&nbsp;&nbsp;04-04-2023y20&nbsp;&nbsp;&nbsp;&nbsp;05-04-2023x30&nbsp;&nbsp;&nbsp;&nbsp;06-04-2023y40&nbsp;&nbsp;&nbsp;&nbsp;07-04-2023x50&nbsp;&nbsp;&nbsp;&nbsp;08-04-2023y60&nbsp;&nbsp;&nbsp;&nbsp;09-04-2023x70&nbsp;&nbsp;&nbsp;&nbsp;10-04-2023y80&nbsp;&nbsp;&nbsp;&nbsp;03-05-2023x90&nbsp;&nbsp;&nbsp;&nbsp;04-05-2023y100&nbsp;&nbsp;&nbsp;&nbsp;05-05-2023x110&nbsp;&nbsp;&nbsp;&nbsp;06-05-2023y120&nbsp;&nbsp;&nbsp;&nbsp;07-05-2023x130&nbsp;&nbsp;&nbsp;&nbsp;05-07-2023y140&nbsp;&nbsp;&nbsp;&nbsp;06-07-2023x150&nbsp;&nbsp;&nbsp;&nbsp;07-07-2023y160&nbsp;&nbsp;&nbsp;&nbsp;08-07-2023x170&nbsp;&nbsp;&nbsp;&nbsp;09-07-2023y180&nbsp;&nbsp;&nbsp;&nbsp;10-07-2023x190&nbsp;&nbsp;&nbsp;&nbsp;11-07-2023y200&nbsp;&nbsp;&nbsp;&nbsp;12-07-2023x210&nbsp;&nbsp;&nbsp;&nbsp;13-07-2023y220&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table: Filtered Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dateproductsales&nbsp;&nbsp;&nbsp;&nbsp;10-04-2023y80&nbsp;&nbsp;&nbsp;&nbsp;03-05-2023x90&nbsp;&nbsp;&nbsp;&nbsp;04-05-2023y100&nbsp;&nbsp;&nbsp;&nbsp;05-05-2023x110&nbsp;&nbsp;&nbsp;&nbsp;06-05-2023y120&nbsp;&nbsp;&nbsp;&nbsp;07-05-2023x130&nbsp;&nbsp;&nbsp;&nbsp;05-07-2023y140&nbsp;&nbsp;&nbsp;&nbsp;06-07-2023x150&nbsp;&nbsp;&nbsp;&nbsp;07-07-2023y160&nbsp;&nbsp;&nbsp;&nbsp;08-07-2023x170&nbsp;&nbsp;&nbsp;&nbsp;09-07-2023y180&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Based on the ownership percentages and filtered sales data, I would like to create a final report as a table visual in Power BI.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table: Final Report&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OwnerTotal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sanket615&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dattu815&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The calculation for the final report can be understood from the table below:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table: Calculation Details&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ownerfyfqproductperctotal(ignore owner)sharedattufy24q1x50330165sanketfy24q1x50330165dattufy24q1y3030090sanketfy24q1y70300210dattufy24q2x70320224sanketfy24q2x3032096dattufy24q2y70480336sanketfy24q2y30480144&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Share = Total (Ignore Owner) * Percentage&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

barritown · Accepted Answer

Hi&nbsp;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.&nbsp;In plain text for convenience:Measure = 
VAR Tbl = 
ADDCOLUMNS ( 
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( Ownership[owner] ),
            SUMMARIZE (
                ADDCOLUMNS ( Sales, 
                    "qtr", 
                    VAR _temp = QUARTER ( [date] ) 
                    RETURN "q" &amp; 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" &amp; 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 &amp;&amp; [fq] = CurrentQtr &amp;&amp; [product] = CurrentProduct &amp;&amp; [fy] = CurrentFY ), [percentage] ) ),
    "share",
    [total sales] * [productperc] / 100 )
RETURN SUMX ( Tbl, [share] )Best Regards, AlexanderMy YouTube vlog in EnglishMy YouTube vlog in Russian

Forum Discussion

Help in dax

1 Reply

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX

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