cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## Help in 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
1 ACCEPTED SOLUTION
Super User

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 =
CROSSJOIN (
VALUES ( Ownership[owner] ),
SUMMARIZE (
"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

Super User

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 =
CROSSJOIN (
VALUES ( Ownership[owner] ),
SUMMARIZE (
"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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors