Using New Measure, SUM all Defects with Filter for Fiscal Year

[jprlimey]

New to Power BI, just trying to navigate my way around.....

I tried the new measure below in Table Data unsuccessfully.

FY18 Total by Defect = Calculate(Sum(Data[Amount]), Filter(Data,Data[Date]) = dDate,dDate[FiscalYear]= "FY18")

I do have a one to many relationship from the Table dDate[Date] to Table Data[Date], thus i thought the above would work.

Ideally i want to determine for each Defect type (in table Data[Defect]) a total for each Defect by Fiscal Year. But first i must get past the Fiscal Year obstacle.

I also played around with adding several New Columns in another table called DefectType where i list each Defect type (unique defect types only). In this case for each Fiscal Year FY18, FY19 & FY20 (in seperate columns) i would sum the amount for each Defect type using the amount in Table Data[Amount].Again i have a one to many relationship from Table DefectType[Defect] to Table Data[Defect].

I need some assistance to get the best approach to solving this.

Thanks

[jprlimey]

I created a sample file, only to realize that I can't upload, company doesn't permit Dropbox etc.

The sample file i created relationships are shown below.

RelationshipsRevenue table

dDate table

Relationships..

Models table to Sales table in linked via Model

dDate table is linked to Sales table via Date

Revenue table is linked to dDate table via Fiscal Year

Sales table

The Revenue table is shown detailing for each FY total units sold and total revenue

The Sales table contains the many observations.

The Sales table is also shown above, with 5 columns Date, Model Manufacturer, Qty & Amount

My ultimate require would be to show a clusterbar chart where for each Fiscal Year we would show the normalized % of total sales by Model / FY Revenue. As an expansion to this i would probably do the same for Manufacturer / FT Revenue.

For example let's say we have sales of Corrola's at $3,889,692 for FY20, with FY20 total revenue at $21,421907 results in 18.1%

I did add captions to the images so i hope they showup.

Thanks

[v-jayw-msft]

Hi @jprlimey ,

I can think about pivot column feature and ALLEXCEPT() function but we need some sample data to confirm.

Can you show some sample data and the result you want to us if you don't have Confidential Information?

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490